Find Cell & Return Values from Cells Above

[andy_mcf]

Hi everyone!

My problem is this - and I'm sure there's a way around it, but I'm running out of ideas...

I have at the top of the worksheet dates going horizontally across (i.e. 2-July, 3-July, etc.) with work timetables for a number of people underneath each day. Some staff, on holidays, have 'hols' written in. What I'd like is to get it so that I can have one cell at the end of each person's timetable, showing the days they're on holidays written out rather than having to scroll across looking for where 'hols' is written.

So, Excel would need to look along that persons range (say A7:ZZ7) for 'hols', then wherever it finds an instance, return the date from 4 rows above (which is written out for each day already) and print it in that particular cell at the end. Ideally, it would be even better if it could write all holidays out - i.e. return a value of 4-July, 5-July, 6-July, 8-October, so on, so everything could be viewed for that person in one go.

Hope that makes sense and thanks if anyone's any ideas - so far I've tried variations of IF, FIND, OFFSET and going mad...and none are working...

[K m]

why not post an example workbook