Thread: Wildcard Element in Indirect Worksheet Name Reference

1. Wildcard Element in Indirect Worksheet Name Reference

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

2. 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. 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!

4. 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"))

5. 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

6. 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"))))`

7. 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?

8. 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.

9. I implemented, but I got the #REF! error.

Page 1 of 3 1 2 3 Last