Indirect Lookup

[CraigPrice]

Hi,

I am currently building a Rota in excel in which each colleague has their own "holiday" sheet. If there is an entry on the users sheet (want to reference the sheet by the name in cell D5 for example) and if there is an entry on their sheet or within the date in cell G3 for example then it returns a value. Specified on the holiday sheet.

If the above returns no value then it would populate with their standard Rota pattern.

I'm struggling here and can't seem to get it to pick up when I'm trying to dynamically pick up the sheet name via the value in D5. If I manually specify the person's name to reference the sheet it works.

 

[CraigPrice]

I can't get access to my formula at the moment as I'm on mobile but will provide asap, just hoping someone had come across something similar before.

The dates run for the year across row 3 from column G onwards. Names are from Column D row 5 onwards.