A shot in the dark.....

[chickadeejen]

Hello,

I'm not sure if anyone will understand my next challenge, but I will try and explain.

Sheet 1 (Calendar Overview, Jan, Feb, Mar.....)
Col B (Person's name )
Col C (start date of their holidays)
Col D (End date of their holidays)
From Col E to the end of a month, I have this formula =AND(E$4>=F8$C7,E$4<=$D7), which highlights the cell and gives me a value (visual gantt view)

At the moment, I'm cutting and pasting the dates every week into Col C & Col D from Sheet 2 which has other data, but feeds the data for sheet 1:
Col B (Month eg. Feb)
Col C(Person name)
Col D (start date of their holidays)
Cold E (End date of their holidays)

I then do a filter on Col B (Months) on Sheet 2, copy & paste into Sheet 1 and then my formula highlights when they will be on holiday. This manual work is taking ages, cutting & pasting, cutting & pasting....and so on!

What I would like for Sheet 1 (gantt view) is for Col C & D to be popluated by sheet 2 Col D & Col E.
FYI: Col B (in sheet 1 - never changes, it's people from the group)

I will be very amazed if someone can understand this....It would be easier if I could do a screen shot, but alas!

Cheers
Jen

 

[Yard]

You mean you want to look up the start and end dates from Sheet2, using the person's name as the key?

In Sheet1 col C

=VLOOKUP(B2,Sheet2!$C$1:$E$10,2,0)

In Sheet1 col D

=VLOOKUP(B2,Sheet2!$C$1:$E$10,3,0)

Is it as simple as that?