Vasweetheart05
New Member
- Joined
- Nov 5, 2016
- Messages
- 27
Hello,
Just to give a heads up, the Dynamic Ranges using Offset will not work based on the way my data is set up.
I have 1 sheet with about 4 sections, each section is separated by a blank space. All of the data is linked to an external document which updates 3x's daily using a cell reference. Since the document is updated daily, each of the sections can be expanded at any given time with new products/items.
Currently, I have each section labeled as a Named Range such as "Revenue_MonthQtrYTD."
On other sheets, I use the Named Range in formulas with the Indirect function.
The problem I'm facing is that each time a new product is added, i have to adjust the named ranges which could lead to errors as I may miss a few. (Reminder: I use this concept for my entire workbook so its in multiple places for large data-sets).
Solution: I tried to automate the name ranges but I can't use Indirect with a Dynamic Name Range. The indirect is a must because i have multiple formulas looking at this data.
Question: Is there anyway that I can create an automated range without using a Dynamic Name Range which will work with an INdirect function? Or anything that could possibly just make updating ranges easier or more automatic? The goal is basically to have each 3 name ranges for the example shown below automatic adjust each time a new product is added.
Example below using only 3 sections (my data set will contain more rows and columns) just to give an idea of the dataset.
<tbody>
</tbody>
Just to give a heads up, the Dynamic Ranges using Offset will not work based on the way my data is set up.
I have 1 sheet with about 4 sections, each section is separated by a blank space. All of the data is linked to an external document which updates 3x's daily using a cell reference. Since the document is updated daily, each of the sections can be expanded at any given time with new products/items.
Currently, I have each section labeled as a Named Range such as "Revenue_MonthQtrYTD."
On other sheets, I use the Named Range in formulas with the Indirect function.
The problem I'm facing is that each time a new product is added, i have to adjust the named ranges which could lead to errors as I may miss a few. (Reminder: I use this concept for my entire workbook so its in multiple places for large data-sets).
Solution: I tried to automate the name ranges but I can't use Indirect with a Dynamic Name Range. The indirect is a must because i have multiple formulas looking at this data.
Question: Is there anyway that I can create an automated range without using a Dynamic Name Range which will work with an INdirect function? Or anything that could possibly just make updating ranges easier or more automatic? The goal is basically to have each 3 name ranges for the example shown below automatic adjust each time a new product is added.
Example below using only 3 sections (my data set will contain more rows and columns) just to give an idea of the dataset.
Revenue | |||
Products | Monthly $ | Qtrly $ | Yrly $ |
Apples | $10 | $30 | $60 |
Bananas | $ | $ | $ |
Oranges | $ | $ | $ |
<>blank row<> | |||
Operating Expense | Monthly $ | Qtrly $ | Yrly $ |
Apples | |||
Bananas | |||
Oranges | |||
<>blank row<> | |||
Gross Margin | Monthly $ | Qtry $ | Yrly $ |
Apples | |||
Bananas | |||
Oranges |
<tbody>
</tbody>
Last edited: