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