Dynamically look up values in sheets based on sheet name

[dbaillie]

Windows 10, Excel 2016

I'm trying to make a formula that adds up the values in cells A1:D1 of all worksheets with "Data" in the worksheet name.

I created a named range "sheetnames" with 

="'"&MID(GET.WORKBOOK(1)&T(NOW()),FIND("]",GET.WORKBOOK(1))+1,255)&"'!"

to pull all the worksheet names, add single quotes and an exclamation mark.

Then I filter that into a new named range "data_sheets" with

=IF(ISNUMBER(SEARCH("Data",sheetnames)),sheetnames)

Which pulls out all the sheet names that contain "Data", and replaces the rest with FALSE.  So if I have worksheets "Test", "Data 1", "Data 2", "Data 3", "Not 4", then data_sheets is equal to {FALSE,"'Data 1'!","'Data 2'!","'Data 3'!",FALSE}

If "Data 1" has 1,2,3 in A1,B1,C1, "Data 2" has 4,5,6, and "Data 3" has 7,8,9, then the sum should work out to 45.

I try the array formula 

=SUM(IFERROR(INDIRECT(TRANSPOSE(data_sheets)&TRANSPOSE({"A1";"B1";"C1";"D1"})),0))

but it returns 0.  If I manually evaluate (press F9 with it highlighted) the INDIRECT function first, and then evaluate the rest of the formula, I get the expected 45.  But if I evaluate the IFERROR without first evaluating the INDIRECT, it replaces all the entries with 0 instead of just the errors, and then the sum is zero.

Is there any way to get IFERROR to behave "correctly" with INDIRECT in this case?  Or is there any way to trim the "FALSE" values from the named range data_sheets?

[#2]

@dbaillie Perhaps easier to spend some time reorganising the workbook and put all Sheets with "Data" in the name, next to each other. Add an empty sheet directly before and after this group of sheets and call them, for instance, Dfirst and Dlast.

Then you can use a formula like in the picture below to do the SUM you described.

When you add another "Data" sheet, just make sure that it is positioned between the Dfirst and Dlast sheets. By the way, I had the numbers 1,2,3 and 4 in A1:D1 on each of the 3 Data sheets. Hence the SUM of 30.

[#3]

@dbaillie 

With an Helper sheet (can be hidden) in the attached file

As notes only:
- If you're sure you'll always target A1:D1 in each 'Data' sheet why not adding A1:D1 at the end of your 'sheetnames' named formula?
- Given that the file must be macro enabled (due to the legacy GET.WORKBOOK function - how long will MSFT maintain it...???) a few VBA lines of code would easily put your 'Data' sheet names on an Helper sheet

[#4]

@Riny_van_Eekelen Thanks.  This looks like a good solution, as long as I can trust my users to duplicate the worksheet between the existing ones.

Is there any way to reference all the sheets without referring to specific sheets?  The first and last sheets of the workbook should be consistent, so if there isn't a way to refer to all sheets, I'll refer to them.

Is there any way to use the sheet ranges with sheet-specific named ranges.  If I have Sheet1, Sheet2, Sheet3, with Sheet1!NamedRange, Sheet2!NamedRange, and Sheet3!NamedRange, respectively, is there any way to do SUM(Sheet1:Sheet3!NamedRange)?  A quick test gives me a #NAME? error.  Then t shouldn't be too hard to add some error checking for the sheets that don't have the named range.

[#5]

@L z. Thanks for the response.  Yes, adding A1:D1 to the named formula would have simplified things.  My oversight.

I realized I would need macros enabled only after I spent a good chunk of my day trying to figure out how to make this work. Had I known that, I would have gone straight to VBA.

Aside, I got it working without the helper sheet like so (with your update adding A1:D1):

=SUM(IFERROR(SUMIF(INDIRECT(data_sheets), "<>"),0))

INDIRECT appears to work as an array formula only inside certain other functions (SUMIF, COUNTIF, SUM, probably others) but not in others (specifically in this case, IFERROR).  I imagine this has something to do with the underlying implementation of the different functions, but I don't have the skill or time to dig more into it.

[#6]

@Riny_van_Eekelen

With your response as an inspiration, I tried to implement the following:

=SUM(FirstSheet:LastSheet!A1:D1*ISNUMBER(SEARCH("Data",MID(CELL("filename",FirstSheet:LastSheet!A1:D1),FIND("]",CELL("filename",FirstSheet:LastSheet!A1:D1))+1,31))))

However it appears that CELL won't work on the array of references, it gives a #VALUE! error.

Basically I figured I could sum A1:D1 on all the sheets, multiplying them by the output of ISNUMBER, which is checking whether each cell is on a sheet with "Data" in the sheet name to zero out the ones from other sheets.

Is there any way to make something like this work?

[#7 · Accepted solution]

@dbaillie Difficult to visualise what exactly you are trying to achieve. Allowing users to add sheets to their liking and trying to capture every possible mistake they might make isn't all that easy. For instance, how do you prevent them from making spelling errors when they name the new sheet, or how do you intend to capture a sheet name like "Dta4" which should have read "Data4"? You'll probably spend 20% of your time making a good looking template and 80% to make it "fool" proof. Not my line of expertise, I'm afraid. 

With named ranges, scoped to single worksheets, perhaps PowerQuery can help. All it requires is a Refresh. The attached workbook contains a very simplistic PQ solution. It finds all named ranges called "data" in sheets where the name contains "Data" or "data". Perhaps something you could use.

[#8]

This is exactly what I needed, thanks.

In general, the intention is to have one Data worksheet for specific equipment tests, but some pieces of equipment need additional runs of some of the tests with different parameters, so I want the user to be able to just duplicate the sheet for additional runs. I don't expect they would change the name, but with your example I can just use a unique named range that gets copied with the sheet. As long as I make it something that wouldn't get accidentally repeated on another sheet, there should never be a problem unless someone deliberately changes the named range on a duplicated sheet. I don't even need to worry about whether they rename the sheet incorrectly.