# A shot in the dark.....

##### Board Regular
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

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Yard

##### Well-known Member
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?

Replies
2
Views
1K
Replies
5
Views
486
Replies
3
Views
446
Replies
1
Views
236
Replies
25
Views
3K

1,191,218
Messages
5,985,325
Members
439,958
Latest member
qb0000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back