Oftentimes you need to calculate (sub)totals of ranges to which data is to be added at a later stage. It would be nice however if you wouldn't need to adjust references in formulas every time data is added to the file, it is more practical to let Excel do this kind of housekeeping for you.
By using the OFFSET worksheet function the exact range containing data can be made dynamic. The OFFSET function has these arguments:
Suppose you have a table like shown in Fig. 14 and you want a range that refers to the apples column, adjusting itself for the number of regions filled in in column A:
Fig. 14, A Data table
You should use cell B2 as the starting address, go down 0 rows and 0 rows to the right. Then count the number of rows that the datatable has in column A (for this you can use the COUNTA function, which counts all filled cells in its range argument) and finally, use a single column. The end result should look like this:
Because you don't want to include the row headings in the count, 1 is subtracted from the COUNTA result.
This formula has been defined as ApplesDynamic, see fig. 15:
Fig. 15: Definition of a dynamic name (note the use of the semicolon list separator in the formula, most users have to use the comma instead)!
Because column A has been used to count the number of rows that are filled, the actual range this new dynamic name refers to will always be determined by how many cells in column A actually contain information. Because of this, you cannot enter any information in cells below the current table, or leave rows empty inside the table: The dynamic range will be off by the number of empty cells or by the number of extra used cells below the table.
When you now enter a new area into the table (like Australia in cell A5), the name ApplesDynamic will automatically refer to the range B2:B5. So when a sales amount for Australian Apples is entered, it will automatically be used in any formulas that use ApplesDynamic. To ensure the ranges next to the ApplesDynamic range are dynamic as well you can again apply the OFFSET function. This time it will be derived as an offset from the ApplesDynamic range: PearsDynamic will be defined as:
In other words: PearsDynamic is simply ApplesDynamic, but shifted one column to the right. The last one is defined using the same method:
Finally, you can make the data area of the entire table dynamic as well, so addition of fruits is accounted for automatically:
As you can see, both the number of rows and the number of columns are here determined using the COUNTA function.
Whether or not the names have been defined properly is easy to check. Hit F5 (Goto) and type a name to check into the Reference textbox and hit OK. Excel will select the computed area the name currently refers to.
A practical example of using dynamic names in combination with a chart is shown in autochrt.zip.