Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a workbook template where the first worksheet will use data from other worksheets, and the other worksheets will be replaced by a regular database export.

In several cases, the information on the summary worksheet will refer to the subsequent data worksheets as a range for a pivot table. The problem is that it is unknown how many rows will be in the range. When a range like A$:D$ is used as the source for the pivot table, there is a "(blank)" item which shows up. This can be suppressed with the filter feature, however I'm concerned that this will make the template miss some rows if new categories are added.

It seems like the problem with this is the range not being confined to the part of the worksheet which actually contains any data.

Is there a way to have a range automatically terminate at its last row with data?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

There are multiple ways to achieve dynamic ranges:

Using OFFSET and COUNTA in a named range

If you use the =COUNTA($A:$A), you'll receive all items in column A. Make sure that you pick a column that is always filled, e.g. the ID column. If your data table starts somewhere further below, you must also account for that: =COUNTA($A:$A)-COUNTA($A$1:$A10).

Now, you can use the formula =OFFSET($A$1,0,0,COUNTA($A:$A),4) to determine a range starting in A1 that is 4 columns wide and contains all rows.

The trick to use this defined range is to enter it as a named range! To do this, go to the Names Manager (in the Formula tab) and insert a new name (e.g. data). Here, instead of providing the reference to a fixed range, copy in the above formula. (Note: if you want to edit the formula, don't forget to press F2 before using the arrow keys, else the formula will be messed up by cell references). Make sure that you use the $ for all cell references.

You can now use the name data as a source for your pivot. If you want to verify that the full range is used, just enter the name in the Name box left of the formula bar (you need to actually type it there) - or just click on the small icon next to the formula in the Name manager.

Using INDEX and COUNTA

The small disadvantage of OFFSET is that it's volatile, i.e. that it is calculated with every calculation run, even if the referred data is not changed at all. This might slow your model down in case you use the dynamic name frequently in formulas.

To avoid this, replace OFFSET with INDEX in the following way:

=INDEX($A:$A,COUNTA($A:$A)):INDEX($D:D$,COUNTA($A:$A))

This will do the same job, but is non-volatile. In addition, if you insert a new column before column D, this formula will adjust while in the former option you'd have to replace the 4 with a 5 (or use COUNTA($1:$1)).

Use Excel table

A much more elegant way than named ranges are Excel tables. If you import data, Excel usually stores the imported data already in a table (you can recognize it by its alternating row color and the Table tab that is available in the ribbon upon selection). Alternatively, you can insert a Table manually (Insert tab - Insert Table).

Provide the table with a speaking name (in the name field in the Table tab) - and then simply use this name as the source of your pivot! If the data changes, the table and therefore the associated range for the pivot will adjust automatically.

However, please be aware that you'll still need to refresh your pivot table(s) after any update!

share|improve this answer
    
Turns out I can use the third option. It doesn't look the same as a table, but when SSIS exports the data, the exact data range appears to have the same name as the tab it creates. Now I have to see how to combine this with the hidden row workaround to handle making the data not be treated as numbers stored as text... –  Cade Roux Jan 24 '13 at 3:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.