Hi there

I am trying to figure out how to change the absolute reference created
automatically by GETPIVOTDATA to a cell reference.

E.g. =GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]","[Group].[All
Group].[Product A]"

I need to change the .[Product A] part to some cell reference like A14
to be able to copy down the whole formula.
Moreover the GETPIVOTDATA usually consist of multiple references which
get created automatically and it is a nightmare to copy them.

Any help is appreciated.

Thanks, Pab

The multiple references is something that you can not avoid, you will have as
many references as dimensions you are looking in the pivot table.
In order to use cell references, the contents of the cell must have the same
format as the one you see on the GETPIVOTDATA function. That is, if you want
to refer the product on cell A14 the contents of cell A14 should be:
[Group].[All Group].[Product A]
And then the formula can be writen as:
=GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]",A14)

I had to make one of these tables, and because the contents of the cell are
not too visually atractive I created an auxiliary table on other worksheet,
relating the caption that I wanted to show with the value that the formula
needed. Then I used VLOOKUP in the report worksheet, hiding the columns and
rows where the data for the formula was.

Hope this helps,
Miguel.

" wrote:

Hi there

I am trying to figure out how to change the absolute reference created
automatically by GETPIVOTDATA to a cell reference.

E.g. =GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]","[Group].[All
Group].[Product A]"

I need to change the .[Product A] part to some cell reference like A14
to be able to copy down the whole formula.
Moreover the GETPIVOTDATA usually consist of multiple references which
get created automatically and it is a nightmare to copy them.

Any help is appreciated.

Thanks, Pab

Thanks Miguel!

Your advice works, indeed.
I was starting to think that there is no way out, so I actually filled
the whole database pointing at the pivot table manually.

The only drawback I just found is that for cascaded dates, the
GETPIVOTDATA generates something like "[Date of Sales]","[Date of
Sales].[All Date of Sales].[2006].[Quarter 1].[January]", while in the
related excel database, I have separate rows for these three dimensions
(Year, Quarter, Month).
I believe there probably is a simple way how to concatenate these three
rows into one cell in a auxilliary row, so if you know how, please let
me know.

Good evening
Pab


Miguel Zapico wrote:
The multiple references is something that you can not avoid, you will have as
many references as dimensions you are looking in the pivot table.
In order to use cell references, the contents of the cell must have the same
format as the one you see on the GETPIVOTDATA function. That is, if you want
to refer the product on cell A14 the contents of cell A14 should be:
[Group].[All Group].[Product A]
And then the formula can be writen as:
=GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]",A14)

I had to make one of these tables, and because the contents of the cell are
not too visually atractive I created an auxiliary table on other worksheet,
relating the caption that I wanted to show with the value that the formula
needed. Then I used VLOOKUP in the report worksheet, hiding the columns and
rows where the data for the formula was.

Hope this helps,
Miguel.

" wrote:

Hi there

I am trying to figure out how to change the absolute reference created
automatically by GETPIVOTDATA to a cell reference.

E.g. =GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]","[Group].[All
Group].[Product A]"

I need to change the .[Product A] part to some cell reference like A14
to be able to copy down the whole formula.
Moreover the GETPIVOTDATA usually consist of multiple references which
get created automatically and it is a nightmare to copy them.

Any help is appreciated.

Thanks, Pab

If you have them writen exactly as they are in the cube, you can concatenate
them in a row, something like:
="[Date of Sales].[All Date of Sales].[" & A1 & "].[" & A2 & "].[" & A3 &"]"
Where the year is in A1, the quarter in A2 and the month in A3.

Hope this helps,
Miguel.

" wrote:

Thanks Miguel!

Your advice works, indeed.
I was starting to think that there is no way out, so I actually filled
the whole database pointing at the pivot table manually.

The only drawback I just found is that for cascaded dates, the
GETPIVOTDATA generates something like "[Date of Sales]","[Date of
Sales].[All Date of Sales].[2006].[Quarter 1].[January]", while in the
related excel database, I have separate rows for these three dimensions
(Year, Quarter, Month).
I believe there probably is a simple way how to concatenate these three
rows into one cell in a auxilliary row, so if you know how, please let
me know.

Good evening
Pab


Miguel Zapico wrote:
The multiple references is something that you can not avoid, you will have as
many references as dimensions you are looking in the pivot table.
In order to use cell references, the contents of the cell must have the same
format as the one you see on the GETPIVOTDATA function. That is, if you want
to refer the product on cell A14 the contents of cell A14 should be:
[Group].[All Group].[Product A]
And then the formula can be writen as:
=GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]",A14)

I had to make one of these tables, and because the contents of the cell are
not too visually atractive I created an auxiliary table on other worksheet,
relating the caption that I wanted to show with the value that the formula
needed. Then I used VLOOKUP in the report worksheet, hiding the columns and
rows where the data for the formula was.

Hope this helps,
Miguel.

" wrote:

Hi there

I am trying to figure out how to change the absolute reference created
automatically by GETPIVOTDATA to a cell reference.

E.g. =GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]","[Group].[All
Group].[Product A]"

I need to change the .[Product A] part to some cell reference like A14
to be able to copy down the whole formula.
Moreover the GETPIVOTDATA usually consist of multiple references which
get created automatically and it is a nightmare to copy them.

Any help is appreciated.

Thanks, Pab