Forum Discussion
Help with Dynamic Named Range
I'm trying to have 1 master sheet in front, in which with drop-down, I select the sheet name using Lists (Data Validation) and it fetches the complete sheet data in there.
Definitely possible.
I have data in my multiple sheets within a workbook (Data is similar in all sheets but subject to get new columns added week on week).
The question that needs to be answered first, I think, is how that similar data on each of those multiple sheets are arranged. What do the different sheets represent (product lines, projects, etc) and what do the additional columns that get added weekly represent? (new weekly progress, ....etc)
That needs to be known, because the ease with which data can be pulled from all those sheets, the formula and functions that would do it, could be quite different, depending on the arrangement of data on each of those multiple sheets.
If it's possible without violating confidentiality, please post a copy of the workbook--or a mockup that faithfully represents the real--on OneDrive or GoogleDrive with a link pasted here. That would help us help you.
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.
For example, here's a sample I tried to recreate based on my original dataset. So, let's say the sheets here are my multiple businesses and in my master sheet, I just need the data based on the Sheet name's drop-down. I know that in the Formulas tab, under Name Manager, we could achieve this without the use of any nested formulas.
See how this looks to you. Simple rearrangement of your sample data. Simple use of UNIQUE to create the list for the data validation drop down. Simple use of FILTER to create the master display.