+ Reply to Thread
Results 1 to 9 of 9

See the duties that a particular person is doing on every day of the week, for each hour

  1. #1
    Registered User
    Join Date
    11-05-2018
    Location
    Shepperton, England
    MS-Off Ver
    Office 365
    Posts
    29

    See the duties that a particular person is doing on every day of the week, for each hour

    Hi All,

    So this is my most complicated request to date and I just don't know where to start.

    I have a new staff rota I would like to view in different ways. In the attachment, you see I have a "People" tab.

    On here, I want to see the duties that a particular person is doing on every day of the week, for each hour of that day.

    The duties are pulled in from other tabs (each day has a different tab)

    The bit that really gets me, is that the daily rota (for example Monday), will show 2 peoples initials on a certain hour. In my example, I have E and R showing on Monday at 8am.

    On the people tab, if I pull the persons name in with the initial E, I need to see the duties she's doing.

    I have highlighted the important cells in the 3 tabs and very much hoping someone will be kind enough to help me!

    Many thanks in advance!
    Attached Files Attached Files
    Last edited by AliGW; 12-29-2021 at 11:30 AM. Reason: Title changed - please think more carefully about your thread titles in future!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,842

    Re: Complicated formula help needed with If functions, Count of text, Indirect and more

    Both day tabs say 'Monday' in A8, so that will need changing.

    Please confirm your current version of Excel: a new version will make this more straightforward.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    11-05-2018
    Location
    Shepperton, England
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Complicated formula help needed with If functions, Count of text, Indirect and more

    Ah yes, thank you for spotting that. I didn't think we would actually need that though, as the tabs are called different names?

    I am on Microsoft 365, version 16.56 Excel. Thanks

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,842

    Re: Complicated formula help needed with If functions, Count of text, Indirect and more

    Please update your profile with your Excel version.

    Having 365 should make this easier.

  5. #5
    Registered User
    Join Date
    11-05-2018
    Location
    Shepperton, England
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Complicated formula help needed with If functions, Count of text, Indirect and more

    Have done so now. Thank you

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,842

    Re: Complicated formula help needed with If functions, Count of text, Indirect and more

    One way!

    In B4:

    =IFERROR(IFNA(INDEX(INDIRECT($A4&"[TASK]"),MATCH("*"&$A$2&"*",INDIRECT($A4&"["&B$3&"]"),0)),""),"")

    You will need to name your tables according to the day of the week in Name Manager.
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,842

    Re: See the duties that a particular person is doing on every day of the week, for each ho

    Administrative Note:

    I have amended your thread title which, although long, was poor as it was not a description of what you are trying to achieve. Please bear this in mind when creating titles in future to make sure that you meet with the requirements of our rules. Thanks.
    Last edited by AliGW; 12-29-2021 at 11:35 AM.

  8. #8
    Registered User
    Join Date
    11-05-2018
    Location
    Shepperton, England
    MS-Off Ver
    Office 365
    Posts
    29

    Re: See the duties that a particular person is doing on every day of the week, for each ho

    Thats great, thanks ever so much Ali.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,842

    Re: See the duties that a particular person is doing on every day of the week, for each ho

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Complicated Lookup and Sum formula needed!
    By glengoz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2016, 08:06 AM
  2. Replies: 1
    Last Post: 03-03-2015, 08:41 PM
  3. Split complicated text/numbers string with functions
    By babyboomexports in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-27-2013, 12:42 AM
  4. [SOLVED] Formula needed: count if cell CONTAINS certain text
    By Wim in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-01-2013, 08:14 AM
  5. [SOLVED] Count and average formula needed to not count text field
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-07-2013, 05:30 PM
  6. [SOLVED] Nested Count and indirect functions
    By jrbluebox in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-14-2013, 10:54 AM
  7. Complicated Formula/Code needed
    By Andy (Hypnotic_Monkey_Scratcher) in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-27-2005, 05:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1