Unable to copy formula down but changing the lookup array every 8 lines

[AdamStratfull]

Hi All,

I hope the below shows up ok as I had to add spaces to move it about...

I need to find a way to summarise our Rota to show who is working each shift each day. This is only part of the Rota. There is 20+ people and it will be for the whole year...

Date | . JD . | Declan | Stuart | Fernando | ...Chris... | Nathaniel | ... Jez... | Mazen | ...Ben...
01/01 | EDIT | ....... | ... H ... | ...Early... | 10:00-20 | ... H ... | ...Eve... | Morning | ....
02/01 | EDIT | Holiday | ...... | ...Mid ..... | ...Late... | ...Early... | ...Eve... | ............ | ........ |


So need it to look like this for each day:

01/01 EDIT 08:30-18:30 JD
01/01 Early 06:00-14:00 Fernando
01/01 Day 09:00-17:00
01/01 Morn 10:00-22:00 Mazen
01/01 DayL 13:00-23:00
01/01 Mid 14:00-00:00
01/01 Late 15:00-01:00
01/01 Eve 16:00-02:00 Jez
02/01 EDIT 08:30-18:30 JD
02/01 Early 06:00-14:00 Nathaniel
02/01 Day 09:00-17:00
02/01 Morn 10:00-22:00
02/01 DayL 13:00-23:00
02/01 Mid 14:00-00:00 Fernando
02/01 Late 15:00-01:00 Chris
02/01 Eve 16:00-02:00

I found that (=IFERROR(HLOOKUP(MATCH(Sheet3!F3,Jan!A5:AO5,0),Jan!$C$3:$AO$4,2,0),"")) works to get the first result.
But the problem I then had, is when I copy it down the lookup array changes (Jan!A5:AO5) when I need it to stay the same for each day. So I need this to stay the same for 8 lines, then I need to change to Jan!A6:AO6 for the next 8 lines and so on for the rest of the year...

Is there a way to get this to change every 8 lines?
Or is there another way to find who does each shift for each day??

I have tried attaching a copy of what I am working...

Any help would be much appreciated!!

[shukla.ankur281190]

Try

E3

Please Login or Register  to view this content.

drag down