Fill cell with text based on dates and text in another table

**triptich** · 09-21-2013, 08:10 PM

Hello,

I'm badly in need of some help

I have one worksheet setup as a yearly calendar

Several other worksheets (one worksheet per month) are used to keep track of staff names and their rostered days off work

I would like to setup a macro where the names are automatically added to the appropriate calendar cell in the first worksheet once they have been listed in the monthly worksheet.

Something like (IF date in monthly worksheet = calendar cell THEN add Name next to date from monthly worksheet to Calendar cell) Do for entire list of names / dates in monthly worksheet

Thanks in advance

triptich

**Pete_UK** · 09-21-2013, 08:37 PM

Take a look at the attachment that I provided in this thread:

http://www.excelforum.com/excel-form...e-to-tab2.html

It seems to do what you are asking for without using macros.

Hope this helps.

Pete

**triptich** · 09-21-2013, 09:54 PM

Thanks for your quick reply,

Your example has got me a little bit excited, however I got lost trying to decipher which bits I coud co-opt into my own worksheet.

I think this line is the one I need to play with to return the list of people away for the day (multiple people can be away on each day)

=IF(ISNA(MATCH(D4&"_"&$A11,Leave_booker!$A:$A,0)),"",INDEX(Leave_booker!$C:$C,MATCH(D4&"_"&$A11,Leave_booker!$A:$A,0)

The calendar setup in your example has 12 cells per calendar day (for up to 12 staff i guess), I need to list all the names in a single cell if possible.

Any advice on where to go from here?

Would something like this work?

=IF(D3:D5=41275, B3:B5,"No RDO Today") Where B3:B5 would hopefully be a list of the names listed against the date 1/01/2013

thanks

T

**Pete_UK** · 09-22-2013, 04:13 PM

In the attached (amended) file, you list the date and name in the Leave_booker sheet in any order. Then in the Calendar sheet you can select the month and year using the drop-downs in K2 and K3 - the display will automatically adjust.

I've changed the formulae so that up to 6 names can be shown in a single cell for the same day. Copy the formula in cell A2 of the Leave_booker sheet beyond A300 if you need to record more data.

Hope this helps.

Pete