CODEWITHSUNDEEP
excelexcel-formula
Edmond Liu
Edmond Liu

Reputation: 21

Dynamic Name Ranges with Pivot Table Data

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?

Upvotes: 0

Views: 1701

Answers (1)

z33Will
z33Will

Reputation: 49

@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.

Upvotes: 0

Related Questions