Forum Discussion
Help with Dynamic Named Range
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.
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?
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?
I've been thinking about this more, and hope you're willing to hang in here with me for a bit longer. So here's a postscript to what I last wrote. Let me ask that, instead of suggesting a method--"use named ranges, name manager"--you be really clear on how you want the Input end of things, the raw data about each of the companies, to be displayed at the Output end of things. What are the changes that need to be reflected in the Output as we go from week to week? Leave the in between process to me or one of the other frequent visitors to this site.
One of the realities about Excel is that there are always multiple methods, multiple ways, multiple tools, multiple processes, to get from point A to point B. You have seen named ranges, one of those tools, work, so keep referencing it; and that may or may not be the best tool or method in this case.
What we need you to do is to back away from prescribing the treatment and, instead, describe the desired outcome more clearly, regardless of the method used to get there. Leave that latter to me or one of the more experienced users of Excel.
I hope that distinction makes sense.
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.