Wildcard Element in Indirect Worksheet Name Reference

naridox

naridox

New member
Joined
Feb 23, 2017
Messages
30
Reaction score
0
Points
0
Location
NCR
Excel Version(s)
2013
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
 
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!
 
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"))
 
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!

View attachment 2017 Log OrderTrack b.xlsx
 
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:
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?
 
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:
I implemented, but I got the #REF! error.
 
When you get that error it means the reference you are pointing to doesn't exist.

Do you have all 52 Week sheets in the workbook? Are they all spelled correctly and consistently (with no space between Week and week number or before and after)? Are you using the same format as you did in your sample i.e. Week#?

If you say yes to all, then you will need to attach your workbook for me to diagnose. Cleanse it of confidential info.
 
Your sheet is missing sheets from Week3 to Week51. This will give you that error. All sheets must be present for this formula to work, otherwise if some are missing, insert them (you can always hide the sheets if you don't want them visible) or use the method in the link I gave you at the beginning where you list the sheets you have and reference that list.
 
So is the formula looking for 52 separate sheets to draw from? I'm just trying to understand how it works. Thanks for your patience and help!
 
Last edited:
Yes, this formula assumes you have 52 sheets with the "Week" prefix. If any are missing any or don't follow the naming pattern, you will get the #REF! error.

If you don't have 52 sheets, you can change the ROW(1:52) reference to then number of sheets you do have, but they still need to be named consecutively from Week1.

Otherwise, if you have random sheet names, use the method I linked you to at the start of your thread.
 
Hey Y'all,

You could use the =Sheets() Function to return the number of sheets in the workbook then subtract the number of NON Week sheets to avoid having to have all 52 week sheets present. Yes it will make an already long formula longer unfortunately.

HTH :cool:
 
Hey guys,

So I added all 52 sheets, then I implemented the original formula (with the edit), then implemented a modified version to include the third if/then statement, and I'm still getting the #REF!. Your thoughts?

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)),
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"),"<>AT")),"")))
 
We'll need to see the workbook.

Are you sure you are using the "Week#" on every sheet?
Are there any mispelled or with extra spaces at the end?
Are there any sheets that have the #REF! error in any of the referenced columns/cells?
 
Do you mean that every worksheet in the doc HAS to have a "Week " in front of it? I'm trying to have those sheets and then a Dashboard and other sheets.
 
This is a very hard thread to keep up when we get responses once every several days....

No. Not every sheet in the workbook needs the "Week" prefix, but there must at least exist 52 sheets with the Week prefix numbered 1 to 52 consecutively....otherwise you will get the #REF! error.

If you don't have or don't want the 52 sheets from the start, then examine the link I gave you way at the beginning. There is shows how to create a formula for a defined list of sheets to reference.
 
You must log in or register to reply here.
Back
Top