Forum Discussion
Dynamically look up values in sheets based on sheet name
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.
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
Lorenzo 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.