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