Forum Discussion
Help with Dynamic Named Range
So, how I have the data is like this.
In my sheets, I have like 18 rows with different values ranging from numbers, decimals and percentages with conditional formatting. And the new columns that get added every week are just the new dates corresponding to those values. Let's say I have like 15 businesses named across 15 different sheets. So, all I want is I have a drop down list on my master sheet which shows the names of my sheets. When I select that, all data is published on my master sheet.
Are the past weeks' numbers static in value? Or are all numbers subject to change? Asked another way, is the only change from week to week the addition of a new week's worth of info? What do you plan to do when the number of columns becomes so great that it can't be reasonably accommodated on a single screen?
Have you ever considered having a single database with all the businesses' data arrays (since you seem to be saying they're all the same sets of measurements) in that single database, with each row identified by date and business name, followed by all the data? Doing that would take advantage of Excel's ability to extract all of the data for any given business using, say, the FILTER function. That is to say, it would easily create that master sheet you're seeking.
And back to my first request: is it possible for you to post a representative (but anonomyzed) example of the workbook? Seeing is a lot better than trying to interpret verbal descriptions. A functioning workbook is better than an image, but at the very least an image.
- Hi mathetes,
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. 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? Also, shared a sample excel file for your reference into the workbook.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.
- I understand what you're trying to do here. I was already able to get the same, but all I want is fetch the range as it is from the sheets. Since the view here is changing, that isn't what I was looking for. Is it possible with incorporating this into named manager where we could select the range from within the sheets?