Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: Wildcard Element in Indirect Worksheet Name Reference

  1. #1
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013

    Wildcard Element in Indirect Worksheet Name Reference



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

    Good afternoon, XGuru!

    I'm building a dashboard populated via if/then statements ranging across 52 other worksheets (representing the 52 weeks in a year). Each week-based worksheet is titled "Wk ##." Rather than input if/then statements for 52 separate worksheets, I'd rather be able to make an indirect reference with a wildcard in the worksheet name to encompass ALL the sheets with "Wk" in the name.

    =SUMIFS(INDIRECT("'Wk"&"*"&"'!$C:$C"),G3 ...
    Or
    =SUMIFS(INDIRECT("'Wk "&"?"&"'!$C:$C"),G3 ...

    Is there a way to add a * or ? wildcard into the worksheet name, itself, in an indirect formula? It seemed to work for a moment when I had one worksheet name "Wk 1" with a wildcard reference of "Wk "&"*"& ... But once I added a second sheet, errored out.

    Thank you!

    Christian
    Christian
    Information Designer
    National Capital Region

  2. #2
    Acolyte retired007geek's Avatar
    Join Date
    Jun 2017
    Location
    South Carolina, USA
    Posts
    86
    Articles
    0
    Christian,

    You're effectively creating a 3D reference. Unfortunately SUMIF(S) does not support 3D references.

    A sample workbook with expected results could go a long way to a solution.

    HTH

  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016


  4. #4
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013
    Hey guys, thanks for the 3D reference and the link. My week just got really busy, but I'll come back later if I have any other issues. Thank you both so much!
    Christian
    Information Designer
    National Capital Region

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,852
    Articles
    0
    Excel Version
    O365
    You can do it like this with your structured sheet names

    =SUMPRODUCT(SUMIFS(INDIRECT("'Wk "&TEXT(ROW(1:52),"00")&"'!C:C"),INDIRECT("'Wk "&TEXT(ROW(1:52),"00")&"'!D:D"),"x"))

  6. #6
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013
    So I've tried lots of ways, including many y'all posted here, so thank you for your input. retired007geek mentioned that 3D references didn't work with sumifs, though I found tons of other articles online implying it did. After trying anyway, I think he's right, so thanks for that input. I didn't understand Bob Phillips method (just lack of understanding, not blaming Bob).

    I'm attaching the scrubbed doc for your review. It should include all the relevant columns per formula. See G28:G31 for my main attempts at cross-worksheet formulas. I have two worksheets right now, but I wanted to be able to refer to 52 worksheets without putting 52 instances of each formula.

    Thanks everyone!

    2017 Log OrderTrack b.xlsx
    Christian
    Information Designer
    National Capital Region

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    You will have to make minor adjustments to Bob's suggestion based on the way your tabs are named....
    Also, you will need to have every sheet between Week1 and Week52 present in the workbook (none missing) or you will get the #REF! error.

    In R10, try:

    Code:
    =IF($D$24="ALL",SUMPRODUCT(SUMIFS(INDIRECT("'Week"&ROW(1:52)&"'!C:C"),INDIRECT("'Week"&ROW(1:52)&"'!G:G"),$D28,INDIRECT("'Week"&ROW(1:52)&"'!D:D"),G$26)),IF($D$24<>"",SUMPRODUCT(SUMIFS(INDIRECT("'Week"&ROW(1:52)&"'!C:C"),INDIRECT("'Week"&ROW(1:52)&"'!G:G"),$D28,INDIRECT("'Week"&ROW(1:52)&"'!D:D"),G$26,INDIRECT("'Week"&ROW(1:52)&"'!A:A"),$D$24)),SUMPRODUCT(SUMIFS(INDIRECT("'Week"&ROW(1:52)&"'!C:C"),INDIRECT("'Week"&ROW(1:52)&"'!G:G"),$D28,INDIRECT("'Week"&ROW(1:52)&"'!D:D"),G$26,INDIRECT("'Week"&ROW(1:52)&"'!A:A"),"<>AT"))))
    Last edited by NBVC; 2017-09-28 at 08:53 PM. Reason: Noticed some of the ROW() references were incorrect. All should be ROW(1:52)


  8. #8
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013
    I pasted your formula, but it immediately highlighted the page I was on, instead of the "Week" worksheets, which would create a cyclical formula. Thoughts?
    Christian
    Information Designer
    National Capital Region

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    The highlighting is actually on rows 1:52 (scroll down to row 52 and see)... that is caused by the ROW(1:52) in the formula... it will not cause a circular reference.

    Did you get a result?


    * I made some adjustments on the formula I gave earlier.. I didn't change some of the ROW(1:2) for my testing back to ROW(1:52). Please use updated formula.
    Last edited by NBVC; 2017-09-28 at 08:54 PM.


  10. #10
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013
    I implemented, but I got the #REF! error.
    Christian
    Information Designer
    National Capital Region

Page 1 of 3 1 2 3 LastLast

Tags for this Thread

Posting Permissions

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