Populating specific cells with a specific value based on dates entered on another sheet

Hi Everyone,
I was hoping someone could help me out. I have a attached a very basic layout of what I am trying to do. With that said after looking at it. I have two worksheets inside the workbook. One is titled " ENTRY" the other "CALENDAR". My goal is to take the information from the "ENTRY" sheet and populate the respective cells on the sheet called "CALENDER". I have tried a number of different formulas and so far the closest i have got is with:

=IF((OR(AND(ENTRY!$B3:$B5000=$A4), AND(ENTRY!$G3:$G5000>=CALENDAR!$B2), AND(ENTRY!$H3:$H5000<=CALENDAR!$B2)), ENTRY!$E3:$E5000, "SHOP")


My goal here is to have excel recognize the value I have entered into ENTRY!B:B, If the Value on CALENDER!A:A is of equal value then I want to populate the cells on the respective row on CALENDAR! with the value ENTRY!C:C from the same row that the matching value is from. However I only want the value from Entry!C:C to populate the cell that fall with in the date range that has been specified in ENTRY!D:E.

Thank you all for your help.

Would you prefer the Calendar sheet to look like a monthly calendar, i.e. with day blocks across (Sunday to Saturday) and then 4 or 5 weeks down for each month, with the appropriate entries shown on the appropriate day? You could select the month and year of interest using drop-downs.

Pete

Originally Posted by Ochimus

JB20,

Think the attached does what you want?

You can test it by changing the Units, Jobs and dates in the Entry sheet.

It uses an "Index/Match" array (entered with Ctrl+Shift+Enter) against the three criteria:

{=IFERROR(INDEX(ENTRY!$C$3:$C$8,MATCH(1,IF(B$2>=ENTRY!$D$3:$D$8,IF(B$2<=ENTRY!$E$3:$E$8,IF($A4=ENTRY!$B$3:$B$8,1)),0))),"SHOP")}

(Look in Col C of the Entry form and find the row where the date in Col D is more than or equal to the date in Row 2 of the Calander sheet, the date in Col E is less than or equal to it, and the Data in Col B matches the row in Col A of the Calender sheet, If there is no match, put "Shop" in the cell,)

Hope it helps

Ochimus

Thank you so much. This has been plaguing me for a while.

This is exactly what I was looking for. I had a feeling it was an array formula. I am however running into another issue. When the start date is any date after 1/1/2016 the job name populates the cells from 1/1/2016 until the end date. For example if job 1 is from 2/1/2016 - 3/28/2016; the calender sheet will populate the respective cells from 1/1/2016 - 3/28/2016. Any thoughts?

Thanks again for all your help

Originally Posted by Pete_UK

Would you prefer the Calendar sheet to look like a monthly calendar, i.e. with day blocks across (Sunday to Saturday) and then 4 or 5 weeks down for each month, with the appropriate entries shown on the appropriate day? You could select the month and year of interest using drop-downs.

Pete

Pete,
I am always game for learning new and alternative ways. If you dont mind Id like to see what your talking about.

Thanks again.

Okay, I played about with it this afternoon. I've added some formulae to your Entry sheet, and put a bit more test data in - this can be in any order, and if you leave the final date off (as in the last example) then it will assume it is only for one day. In the Calendar sheet you can choose a month and year from the drop-downs in column K and the display will automatically adjust, giving you up to 12 Units for each day.

Hope this helps.

Pete

Originally Posted by Pete_UK

Okay, I played about with it this afternoon. I've added some formulae to your Entry sheet, and put a bit more test data in - this can be in any order, and if you leave the final date off (as in the last example) then it will assume it is only for one day. In the Calendar sheet you can choose a month and year from the drop-downs in column K and the display will automatically adjust, giving you up to 12 Units for each day.

Hope this helps.

Pete

Thanks this is great. Unfortunately the end result im trying to get will not work in this format. At least not for this specific application. However I do know where i can use this. Thanks a ton for this info.

If I could just figure out the problem I explained in my response to "ochimus" I would be golden.

Thank you again everyone for your help.

JB,

Been "AFD" since I posted the example, hence no earlier reply.

I tried it with a later date, and found exactly the same problem, which baffles me at the moment because the formula is quite explicit that it should produce the "right" result only if the column date is on or between the dates in Cols D and E of the Entry sheet.

I'm out shortly, but will get onto this tomorrow morning - although knowing this site I suspect you have several replies before then spotting what I have missed!

Ochimus

Originally Posted by Ochimus

JB,

Been "AFD" since I posted the example, hence no earlier reply.

I tried it with a later date, and found exactly the same problem, which baffles me at the moment because the formula is quite explicit that it should produce the "right" result only if the column date is on or between the dates in Cols D and E of the Entry sheet.

I'm out shortly, but will get onto this tomorrow morning - although knowing this site I suspect you have several replies before then spotting what I have missed!

Ochimus

Ochimus,
Ive been playing around with it this morning and ive figured out that all the cells on the earlier dates, that should not be populated are capturing the last value listed in column C on ENTRY!. Any ideas why this would be happening?

Thanks again for all your help.

-JB

That is an array formula - you need to commit it using Ctrl-Shift-Enter (CSE), and you can see curly brackets around the formula when viewed in the formula bar.

If you want to avoid seeing 0 when the Job is blank, you can do this in B4:

=IFERROR(INDEX(ENTRY!$C$3:$C$8,MATCH(1,(ENTRY!$B$3:$B$8=$A4)*(B$2>=ENTRY!$D$3:$D$8)*(B$2<=ENTRY!$E$3:$E$8),0))&"","Shop")

(changes in red). Use CSE to confirm the formula, then copy across and down.

Hope this helps.

Pete

Pete,

Can't think why I said it wasn't an array when every cell in my attachment EXCEPT B4 was arrayed - just my luck that the original still produced the correct output!

I also left the "0" in deliberately, as I didn't know whether JB wants to distinguish between Units where the column was within the date span but no job was assigned, and those where the date was outside the span, whether or not there was a job?

Your update is definitely an improvement on my original, because it gives JB a simple way to apply whichever approach is correct.

Ochimus

Pete & Ochimus,
You guys are awesome. Sorry for the delay, my computer took a turn for the worse and i just go it back up and running. Thanks for all the help. As far as seeing "0". I can just create a conditional format to either match the character color and the background color, or I can simply go to the advance settings and check the box that allows the worksheet to display zeros. Once again Thank you guys so much you both have just expanded my knowledge of excel beyond where I ever thought i would be at. Massive Props to the both of you.

-JB

Appreciated, just glad we could help you out. It'll be your turn in a few years time!

Ochimus