I am using Excel 2007, and have a question about the getpivot function

I have a pivot table set up where there are three rows that contain
different values of levels, and then a fourth row which is just a sum of
customer (so sum of all levels). See below for an example.

I want to use one fomula that i can copy and paste and just reference values
in cells to pull back the data.
=GETPIVOTDATA("Revenue",$A$3,"Name",+e$2,"Level",+ e$1)

The challenge is I don't know what I can put in Cell e1 - to return what is
in the total line (i.e. the sum of all levels). The only way I can get this
is to create another getpivot function where I don't include (field2,item2 -
i.e. "level",+e1) - so now I can't easily copy to additional columns with
different values in rows 1 and 2 -some that might have a level, and sum which
are looking for the sum of all levels.

Name Level Revenue
George A 5
B 10
C 12
George Total 17
David A 3
B 3
C 3
David Total 9

Hoping someone can help, and that I have outlined the issue clearly.

Hi,
I had a similar problem just yesterday.

I found this solution from a few year's back:
http://tinyurl.com/2saafw

Basically, you need to use IF statements to determine the number of
parameters, and execute a GETPIVOTDATA with different numbers of
parameters. It's not very attractive, but it'll work.

Andy

On Oct 4, 4:57 am, Chris Large <Chris
wrote:
I am using Excel 2007, and have a question about the getpivot function

I have a pivot table set up where there are three rows that contain
different values of levels, and then a fourth row which is just a sum of
customer (so sum of all levels). See below for an example.

I want to use one fomula that i can copy and paste and just reference values
in cells to pull back the data.
=GETPIVOTDATA("Revenue",$A$3,"Name",+e$2,"Level",+ e$1)

The challenge is I don't know what I can put in Cell e1 - to return what is
in the total line (i.e. the sum of all levels). The only way I can get this
is to create another getpivot function where I don't include (field2,item2 -
i.e. "level",+e1) - so now I can't easily copy to additional columns with
different values in rows 1 and 2 -some that might have a level, and sum which
are looking for the sum of all levels.

Name Level Revenue
George A 5
B 10
C 12
George Total 17
David A 3
B 3
C 3
David Total 9

Hoping someone can help, and that I have outlined the issue clearly.

Thanks Andy - that worked! Like you said, it is not attractive - but it
works. Have a great day! Cheers, Chris

"AndyCotgreave" wrote:

Hi,
I had a similar problem just yesterday.

I found this solution from a few year's back:
http://tinyurl.com/2saafw

Basically, you need to use IF statements to determine the number of
parameters, and execute a GETPIVOTDATA with different numbers of
parameters. It's not very attractive, but it'll work.

Andy

On Oct 4, 4:57 am, Chris Large <Chris
wrote:
I am using Excel 2007, and have a question about the getpivot function

I have a pivot table set up where there are three rows that contain
different values of levels, and then a fourth row which is just a sum of
customer (so sum of all levels). See below for an example.

I want to use one fomula that i can copy and paste and just reference values
in cells to pull back the data.
=GETPIVOTDATA("Revenue",$A$3,"Name",+e$2,"Level",+ e$1)

The challenge is I don't know what I can put in Cell e1 - to return what is
in the total line (i.e. the sum of all levels). The only way I can get this
is to create another getpivot function where I don't include (field2,item2 -
i.e. "level",+e1) - so now I can't easily copy to additional columns with
different values in rows 1 and 2 -some that might have a level, and sum which
are looking for the sum of all levels.

Name Level Revenue
George A 5
B 10
C 12
George Total 17
David A 3
B 3
C 3
David Total 9

Hoping someone can help, and that I have outlined the issue clearly.