not your typical Pivot Table Named Range question

[Dick Watson]

I assigned a named range to the column headers with in a pivot table in
order to compute a
=count(MyPvtTableColumnHdrs)
of them.

I assigned a named range to the row headers at the outer-most level in a
pivot table in order to compute a
=countif(MyPvtTableRowHeaders,"?????ColValsEndLikeThis")
of them.

Both ranges were created with absolute references.

I expected these ranges to expand and contract as the size of the pivot
table changed with the data.

No Joy. They don't.

Is there any other way to reference a range within the pivot table and have
this reference grow and shrink with the pivot table???

Thanks in advance!

 

[Ken Wright]

Assuming your data tab is called DATA and headers in row1, and no blanks in
one of your columns (Assume A)

=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

Name the above formula DATABASE using Insert > Name > Define

and then just use =DATABASE for the Pivot Table source

Regards
Ken.....................

 

[Dick Watson]

This is "not your typical Pivot Table Named Range question" and I am not
asking about using a named range for the Pivot Table data source. I am
asking about naming a range within the __resultant pivot table__ and having
that range reference resize appropriately as the __resultant pivot table__
expands and contracts.

 

[Herbert Seidenberg]

Excel 2007
Created Defined Name for
Field of PivotTable with macro.
Defined Name is dynamic and moves with PT.
http://www.mediafire.com/file/zoz2tjudmnm/07_19_09.xlsm

 

[Dick Watson]

I'll check it out. Unfortunately the problem at hand has to solve in Excel03
since my forward looking employer is just now starting to evaluate Office07.
They may roll it out some time late this year or next. Deploy no app before
it's obsolete. That's our IT motto.

 

[Dick Watson]

It's uglier than I'd hoped but here's a way I found to do this:

UDF:

Public Function GetPivotFieldRefString(data_field As String, pivot_table As
Range) As Variant

' returns the address of the data_field within the PivotTable containing
Range pivot_table

GetPivotFieldRefString =
pivot_table.PivotTable.PivotFields(data_field).DataRange.Address

End Function


calling formula example (from the sheet with the PivotTable:

=COUNTA(INDIRECT(GetPivotFieldRefString("Year",pvt_WBS2_v_Year)))

Neater suggestions still welcome.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Get Field range.
Added alternate UDF method
as suggested by Charles Williams.
http://www.mediafire.com/file/uwjnjqgfhyt/07_19_09a.xlsm

 

[Dick Watson]

I was too dumb to remember that I needed to SET. Sometimes you get so deep
in one like that you can't see the forest for the grains of sand.

So here's what I did:

Public Function GetPivotFieldRef(data_field As String, _
pivot_table As Range) As Range

' return a ref to the data_field within the PivotTable containing Range
' pivot_table

Set GetPivotFieldRef = _
pivot_table.PivotTable.PivotFields(data_field).DataRange

End Function

Basically the same syntax as GETPIVOTDATA().

Thanks for the help!