1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Staff Roster and Report

Discussion in 'Ask an Excel Question' started by Nicki, Oct 6, 2017.

  1. Nicki

    Nicki New Member

    Messages:
    16
    Hi Guys.
    I have a Weekly roster for nearly 42 staff working in 30 room in different shift in a week. as you can see in weekly rota tab in Spreadsheet the Initial for staff are on the left side and as I enter any initial in time slot (in red coloured time slot).
    PROBLEM: difficult to see one staff location in week in order not to put her in the same room next week.
    I would like to see a generated report for each staff individual by room allocation so that they are not going to the same room for the following next weeks. any other idea for seeing one staff rota in a week, similar to staff rota tab. Please help. Many Thanks

    Attached Files:

  2. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    Try,

    1] In "1 Staff rota" sheet C7, formula copied across and down :

    =IFERROR(INDEX('Weekley Rota'!$B$3:$AM$3,MATCH($E$3,OFFSET('Weekley Rota'!$B$2,MATCH($B7,'Weekley Rota'!$B$2:$B$133,0)+COLUMN(A1)*4-2,,,38),0)),"")

    2] See attached file.

    Regards
    Bosco

    Attached Files:

  3. Nicki

    Nicki New Member

    Messages:
    16
    This is brilliant Thank you so much Bosco, Great job,
  4. Nicki

    Nicki New Member

    Messages:
    16
    Hi Bosco,

    Sorry If I am bothering you again. In case you have a free time could you look at my new table what is wrong with formula in "Report Tab".

    Many Thanks.

    Nicky

    Attached Files:

  5. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    See as per attached revised file.

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and Nicki like this.
  6. Nicki

    Nicki New Member

    Messages:
    16
    Bosco it is amazing the way you sorting out the formula, you are acting like a chess player. Many Thank. Highly appreciated your help.
  7. Nicki

    Nicki New Member

    Messages:
    16
    Hi Bosco

    Please can you amend this formula in order to have G7 column for the 17:50 time slot. Thanks
  8. Nicki

    Nicki New Member

    Messages:
    16
    I manage to dot it by adding a row to the weeklyRoster table. Ignore my last post. Many Thanks.

Share This Page