Find pivot range formula

[jaryszek]

Hi,

i am looking a way to find pivot range and refer to it.
Assume to first column, first cell.

It can be done by pivot name?
It is possible to do it?

i know that i can use OFFSET (volatile function - not for me) or index and sum but still i have to know where pivot table is...

edit: please move the topic to Excel Formulas subforum.

Best,
Jacek

[:) Sixthsense :)]

Using UDF

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Please Login or Register  to view this content.

=GetPivotRange("PivotTableNameHere","WorksheetNameHere")


=GetPivotRange("PivotTable1","pvt1")

[jaryszek]

Hi , thank you.

i think this is only one available way.
Question is how often this function will be calculated?

I want to avoid volatile functions.
The preferred way is to only one time define it because pivot table will be in one place all the time.

Best,
Jacek

[:) Sixthsense :)]

The given UDF is not volatile.

[jaryszek]

Yes,

but when i will add range of pivot table to data validation list and use it in each cell in column table it can be.

I thank i have to use static function here.

Best,
Jacek

[:) Sixthsense :)]

Then No Formula's will do that for you.

Convert the UDF to Sub Routine to make it as static on button press.

[jaryszek]

I can not use UDF as static?