Forum Discussion
Help with Dynamic Named Range
So, in rows it isn't possible since those sheets I get will be from different businesses after collating on their end. So, its crucial I keep the view intact for all businesses in this case.
I'd already created that revised sample, and it seemed to me that each "business" had exactly the same array of results, did they not?
Now, for the old columns as you mentioned, as of now, we're just hiding for the past days data, but are only keeping about 2-4 months data as of now. Is there a way for you to do something with named ranges, where is pulls up the complete range from the sheets?
Not sure if I fully get what you're saying. The FILTER function, which I used, could easily be modified so that all historical data are retained, and the master summary sheet pulls data between specified Start and End dates. If you're not familiar with the FILTER function--it's new in the last couple of years--it has a LOT of power, rendering named ranges less vital, because you can specify multiple dimensions to be used in filtering from a single database.
So unless your data are wildly distinct between different companies, which does not appear to be the case, I think you'd be better off reconsidering your model. I am open to being convinced otherwise.
Here's a variation using your formatting of the data and the INDIRECT function to pull from each corresponding cell in the subordinate sheets. It could be made more elegant (e.g., adding the drop down list), but I first wanted to see if this comes closer to what you were desiring.
FWIW I consider the FILTER approach a far more satisfying approach but I understand you may not have the ability to change your source data.