+ Reply to Thread
Results 1 to 10 of 10

Indirect list help.

  1. #1
    Registered User
    Join Date
    03-28-2020
    Location
    LlanfairPG
    MS-Off Ver
    2016
    Posts
    30

    Indirect list help.

    How can i get excel to return a list of who is on a day off on a particular day ?
    I have 52 sheets and want to create a summary sheet, so when i change the Sheet name in cell C1 on my summary page, it returns a list of who was off on a particular day, for that particular week.
    Your help would be invaluable and very much appreciated. TIA
    Attached Files Attached Files
    Last edited by LlanfairPG; 02-27-2022 at 03:41 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Indirect list help.

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Indirect list help.

    Hi,

    I created a mock-up for you but next time you should be able to upload an example in excel.....
    Try this:

    B5 and across:
    =IF(INDIRECT("'"&$C$2&"'!"&ADDRESS(ROW(),COLUMN()))="","",INDIRECT("'"&$C$2&"'!"&ADDRESS(ROW(),COLUMN())))
    Attached Files Attached Files
    Last edited by Limor_OP; 02-27-2022 at 03:42 PM.

  4. #4
    Registered User
    Join Date
    03-28-2020
    Location
    LlanfairPG
    MS-Off Ver
    2016
    Posts
    30

    Re: Indirect list help.

    Apologies, i have now put up a sample file.

  5. #5
    Registered User
    Join Date
    03-28-2020
    Location
    LlanfairPG
    MS-Off Ver
    2016
    Posts
    30

    Re: Indirect list help.

    Quote Originally Posted by belinda200 View Post
    Hi,

    I created a mock-up for you but next time you should be able to upload an example in excel.....
    Try this:

    B5 and across:
    =IF(INDIRECT("'"&$C$2&"'!"&ADDRESS(ROW(),COLUMN()))="","",INDIRECT("'"&$C$2&"'!"&ADDRESS(ROW(),COLUMN())))
    Yes, thats close to what i need it to do, is there any way of doing as an array formula that matches the day and lists the name from the left hand column when it finds day off in the day column? Sorry to be a nuisance. TIA

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Indirect list help.

    Why do need 52 sheets ? (yes I know it is one per week) but all this could be managed on one sheet.

    You have a 1 million rows and 16K columns per sheet!
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  7. #7
    Registered User
    Join Date
    03-28-2020
    Location
    LlanfairPG
    MS-Off Ver
    2016
    Posts
    30

    Re: Indirect list help.

    it's because every sheet is a rota for 85 staff, which we use to display each week.

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Indirect list help.

    Hi,

    So there must be a better way but this is what I came up with:

    In C4:
    =IFERROR(INDEX(INDIRECT("'"&$C$1&"'!"&"$D$1:$D$15"),AGGREGATE(15,6,ROW(INDIRECT("'"&$C$1&"'!"&"$D$6:$D$15"))/(INDIRECT("'"&$C$1&"'!"&"$E$6:$E$15")<>""),ROWS($C$1:$C1))),"")

    Then in D change the part in red to the subsequent column and so on....
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-28-2020
    Location
    LlanfairPG
    MS-Off Ver
    2016
    Posts
    30

    Re: Indirect list help.

    Quote Originally Posted by belinda200 View Post
    Hi,

    So there must be a better way but this is what I came up with:

    In C4:
    =IFERROR(INDEX(INDIRECT("'"&$C$1&"'!"&"$D$1:$D$15"),AGGREGATE(15,6,ROW(INDIRECT("'"&$C$1&"'!"&"$D$6:$D$15"))/(INDIRECT("'"&$C$1&"'!"&"$E$6:$E$15")<>""),ROWS($C$1:$C1))),"")

    Then in D change the part in red to the subsequent column and so on....
    That Works perfectly, exactly what i needed. If i could add reputation more than once i would.

  10. #10
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Indirect list help.

    Glad it helps you....but didn't you want a list?


    With a list it would be :

    B4 and down:
    =IFERROR(INDEX(INDIRECT("'"&$C$1&"'!"&"$D$1:$D$15"),AGGREGATE(15,6,IF(INDIRECT("'"&$C$1&"'!"&"$E$6:$J$15")<>"",ROW(INDIRECT("'"&$C$1&"'!"&"$D$6:$D$15"))),ROWS($C$1:C1))),"")

    C4 and across:
    =IF(VLOOKUP($B4,INDIRECT("'"&$C$1&"'!"&"$D$6:$J$15"),COLUMNS($A$1:B$1),0)=0,"",VLOOKUP($B4,INDIRECT("'"&$C$1&"'!"&"$D$6:$J$15"),COLUMNS($A$1:B$1),0))
    Attached Files Attached Files

+ 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. Indirect list from another workbook
    By lobo40us in forum Office 365
    Replies: 1
    Last Post: 04-12-2021, 11:17 PM
  2. Developer List Box indirect function
    By Martines91 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-18-2019, 03:15 AM
  3. [SOLVED] Drop Down List - Indirect
    By ozstrik3r69 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-28-2018, 01:00 PM
  4. [SOLVED] Auto update of indirect list when parent validation list is re-selected.
    By xtremca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-23-2015, 10:15 AM
  5. Indirect drop down list not working
    By Narelles in forum Excel General
    Replies: 3
    Last Post: 12-18-2013, 09:16 PM
  6. INDIRECT.EXT and List using Data Validation
    By jimmyyap in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-02-2013, 03:12 PM
  7. Use MID Substring for INDIRECT List
    By hotrod55 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2013, 01:50 AM

Tags for this Thread

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