Automated Ranges using Excel Function

[Vasweetheart05]

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.

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>

 

[thisoldman]

I'm one of those people who thinks INDIRECT is an evil function. I've only used it in anger.

If the data begins in A1, that somewhere in A:A are cells containing the words "Products", "Operating Expenses", and "Gross Margin". A2="Products".
If the data extends to Column D:D.
If there is only one blank row between sections.

These dynamic formulas pick up the three blocks, including each block's headers:

The 'Products' block:
=INDEX($A:$A,MATCH("Products",$A:$A,0)):INDEX($D:$D,MATCH("Operating Expense",$A:$A,0)-2)

The 'Operating Expense' block:
=INDEX($A:$A,MATCH("Operating Expense",$A:$A,0)):INDEX($D:$D,MATCH("Gross Margin",$A:$A,0)-2)

The 'Gross Margin' block:
=INDEX($A:$A,MATCH("Gross Margin",$A:$A,0)):INDEX($D:$D,COUNTA($A:$A)+2)

 

[Vasweetheart05]

The issue with this is that I still can't use the Indirect formula with the Dynamic Range.

The reason I need the indirect is that I have a drop down list for "Month" "QTD" YTD" so based on what the user selects, the dataset on another tab will change the dollar totals based on the list selection.Month QTD YTD are all on separate tabs as well due to the amount of data.

I have 3 sheets (Products Monthly, Products QTD, Products YTD) which have similar sets of data (Revenue, Gross Margin, etc). The 3 sheets are unchangeable as that is how the data comes from the system.

On another tab i have an automated, consolidated tab that shows everything (all products with one dollar amount column) on one sheet with references to the datasource sheets ..

On my automated formula tab, i have the following:

Product (column a)

Dollar amount (column b which automatically adjusts based on the drop down list) >> This is also a vlookup that lookups the dollar amount using the DYNAMIC NAMED RANGE (or named range) as the source reference for the product LOOkup.

 

[thisoldman]

My first stumbling tries with INDIRECT and named dynamic ranges were unsuccessful. I wondered if named structured tables would work and remembered a Contextures tutorial, "Create Dependent Drop Down Lists," http://www.contextures.com/xlDataVal02.html.

Look at the section labeled "Using Dynamic Lists:", http://www.contextures.com/xlDataVal02.html#Dynamic.