Results 1 to 5 of 5

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

  1. #1
    Neophyte williamspage's Avatar
    Join Date
    Apr 2021
    Posts
    3
    Articles
    0
    Excel Version
    16.47

    Help returning information from a messy array - Staff Rota!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files
    Last edited by williamspage; 2021-04-01 at 04:45 PM.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,938
    Articles
    0
    Excel Version
    365
    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. #3
    Neophyte williamspage's Avatar
    Join Date
    Apr 2021
    Posts
    3
    Articles
    0
    Excel Version
    16.47
    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. #4
    Neophyte williamspage's Avatar
    Join Date
    Apr 2021
    Posts
    3
    Articles
    0
    Excel Version
    16.47
    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. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,938
    Articles
    0
    Excel Version
    365
    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
  •