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?

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.

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.

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.

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.

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.

Help with Dynamic Named Range | Microsoft Community Hub

mathetes
Silver Contributor
Aug 29, 2023
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.
- bsrujan022
  Copper Contributor
  Aug 29, 2023
  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.
  - mathetes
    Silver Contributor
    Aug 29, 2023
    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.

Forum Discussion

Help with Dynamic Named Range

Share

Resources