# Thread: Help returning information from a messy array - Staff Rota!

1. ## Help returning information from a messy array - Staff Rota!

Can anyone help me - I have an original staff rota (I have attached the file). The top half is all the dates of the year and the bottom part is all the staff working on that day. On the second work sheet I want to be able to insert a date and it automatically return what staff are working on that day. Is this possible to do? I would appreciate any help!

2. You have Excel version 97-2003 yes? Or at least that's the file type you attached. On the other hand you've said you've got version 16.47 which is an Office 365 version. Confusing.
Anyway, assuming Excel 2003:
Select cells B5:Y5 of Sheet2 and array-enter this formula to all those cells at once:
Code:
`=TRANSPOSE(OFFSET('Master Rota'!\$A\$40:\$A\$63,0,SUMPRODUCT((\$A5='Master Rota'!\$B\$4:\$X\$35)*COLUMN('Master Rota'!\$B\$4:\$X\$35))))`
Array-entering means committing the formula to the sheet with the keyboard combination of Ctrl+Shift+Enter, not just the usual plain Enter.

Now put a valid date into cell A5.

Note that you have a lot of 2020 dates in the grid of the Master Rota; they need converting to 2021 with search and replace.

3. p45cal - thats amazing thank you. I will give that a go. I am using 16.47 but it is an old file that I have inherited in my job. Im trying to make things run a little more efficiently by automating things like this. Thank you for your help.

4. I have given this a go - the formula that you gave returns the information form the day after the date in Cell A5 so I have adjusted the formula to the following:

=TRANSPOSE(OFFSET('Master Rota'!\$A\$40:\$A\$63,0,SUMPRODUCT(((\$A5-1)='Master Rota'!\$B\$4:\$X\$35)*COLUMN('Master Rota'!\$B\$4:\$X\$35))))

However I am having an additional problem. If I enter the any date into Sheet 2-A5 that is in Master Rota column B4 - B35 then it just returns zeros. 8/3/2021 for example.

Thank you so much for your help I really appreciate it.

5. I made a mistake regarding which rows to bring over; I see now that I should have started from row 37 of the Master Rota sheet instead of row 40, but that doesn't explain the day out. What might explain it is if you've eliminated column A from the Master Rota sheet.
The adjustment you made looks for the day before and the date you quote is at one edge of the table. Best if you attach your actual workbook with just those two sheets in.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•