Dynamic Name Ranges with Pivot Table Data

0
votes

I'm trying to create dynamic name ranges using data presented in a pivot table. See below:

enter image description here

I would like to create a dynamic name range that captures this data. Here is what I wrote:

=OFFSET(INDIRECT(CONCATENATE("'R1'!",(CELL("address",INDEX('R1'!$A:$A,MATCH(MIN('R1'!$A:$A),'R1'!$A:$A,0)))))),0,0,COUNTIF('R1'!$A:$A,">1/1/2006"))

The first argument of this offset formula is multi-part: I had to first find the first date in column A using INDEX('R1'!$A:$A,MATCH(MIN('R1'!$A:$A), then find the address of that cell, then present it properly for the offset formula using concatenate (to append the sheet name) and indirect.

For the height of this name range, I used a COUNTIF function to get the amount of dates in the column.

However, when I assign that formula to a name range and tested it in a pivot table, I get the error "Reference isn't valid". Any ideas?

1
excelexcel-formula
If you format your data as a table it will dynamically update the named range for you – urdearboy
Or you can have your pivot data grab entire columns rather to some row. – urdearboy
@urdearboy How do I go about doing either of those two? Thanks for the help. – Edmond Liu

1 Answers

0
votes

@Edmond Liu You should first name the range in on the tab where the data will live. After that then configure the pivot table to place data in the dynamic named range space.

Another option is to place your pivot table in say Column C or D. In column A or B put your dynamic named range along with some logic formulas to extract what you actually need from the pivot table. As the pivot table changes your formulas will automatically update. You can include to IFERROR/ISERROR logic to exclude blank or null cells.