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.