data lookup from another spreadhseet

[intercooler]

Hi all,
i am posting this here as i was not able to think of any formula that could do the desired...

So...i have two seperate spreadsheets, both on a shared drive. (sample ones attached here) One is the daily tasks of my employess, and the other - the schedule.

What i wouldl iike is to be able to populate the shift (row3) with the respective shift for the employee for the schedule for any given day.
I was able to do this with vlookup and match, but i have to re-type the formula in each cell.
Is there any amcro that could be done to get the date, get the cells beneath it and cross-reference with the schedule spreadsheet, returning the shift number.
I have manually input the data for the first 2 days. Dates and employee names are the same in both spreadsheets, but there might be multiple columns for comments after each day, so a fixed solution would not work. And one more thing - the values are not always shift numbers, they could be f.e. vacation as text value.

Appreciate any ideas...

[Richard Buttrey]

Hi,

Maybe in B3 copied to E3

Please Login or Register  to view this content.

if you then copy it to G3 you'll need to change the $B$1 reference to $G$1..and similarly when copied to the next block. It would help if you put the dates in all of B1:E1, G1:J1..etc then you could copy across without needing to change $B$1

[intercooler]

Thank you Richard, and apologies ofr the delay, but unfortunately the formula returns an error.

[Richard Buttrey]

Hi,
Can I suggest you upload the workbook so that we can see the problem in context.