Help with Dynamic Named Range

[bsrujan022]

Hi team, 

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). 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. I know that its possible using Named Range since I saw someone using the same. 

Would anyone be able to help me with this?

[#2]

@bsrujan022 

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.

[#3]

Hi @mathetes,

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.

[#4]

@mathetes,

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.

[#5]

@bsrujan022 

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.

[#6]

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.

[#7]

@bsrujan022 

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.

[#8]

@bsrujan022 

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.

[#9]

@bsrujan022 

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.

[#10]

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?

[#11]

@bsrujan022 

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?

I know that what you're saying is clear to you. It's not to me. When, for example, you say "all I want is fetch the range as it is from the sheets" it seems to me that's exactly what I've done (assuming you're speaking of the last one I posted, the one using INDIRECT). So please clarify.

"Since the view here is changing that isn't what I was looking for" -- what are you referring to with "the view here is changing"? What "view"; what "change"

Your very first post says:  "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." 

But that's what my response did, so far as I understand these words. So help me understand what I'm missing.

[#12]

@bsrujan022 

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.