getpivotdata custom subtotal

A

anna_717717

Hi all, hope somebody can help me

I'm trying to get some data from a pivot table
When I was using automatic subtotals I was able to use the formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)

When I changed to 'min' subtotals I got a #ref error and tried:
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
(where 'Test1' is a 'Process Skill')

Is it possible to modify this formula to include cell references (i.e. B2,
C2, and D2 as above)

Thanks
 
S

Shane Devenshire

Apparently I don't follow your problem. A GETPIVOTDATA function will work
regardless of whether the subtotals are SUM or MIN.

And although I don't understand your question, if you have a formula like

=GETPIVOTDATA("Gross Sales",$G$3,"Region","East")

You can change the "Region" or "East" reference to a cell address, even one
that is not in the pivot table. However, to return any resonable result the
entries in those cells must match a field title.

=GETPIVOTDATA("Gross Sales",$G$3,A1,B1)

Is fine, and would return the same result if A1 contained the text Region
and B1 East.
 
A

anna_717717

Hi Shane
I tried this formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)
but got a #ref error. The only explainations I could find for the error was
that when referencing to a specific cell that the formula excel will
automatically generate (=GETPIVOTDATA($B$15,"Trainee[a] 'Test1'
Level['2';Data,Min]") ) is wrong and has to be corrected to
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
Unfortunately I don't understand how I can change this formaula to reference
cells, for example rather than trainee 'a' it would call up whatever value
was is cell D2.

Thanks

Shane Devenshire said:
Apparently I don't follow your problem. A GETPIVOTDATA function will work
regardless of whether the subtotals are SUM or MIN.

And although I don't understand your question, if you have a formula like

=GETPIVOTDATA("Gross Sales",$G$3,"Region","East")

You can change the "Region" or "East" reference to a cell address, even one
that is not in the pivot table. However, to return any resonable result the
entries in those cells must match a field title.

=GETPIVOTDATA("Gross Sales",$G$3,A1,B1)

Is fine, and would return the same result if A1 contained the text Region
and B1 East.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


anna_717717 said:
Hi all, hope somebody can help me

I'm trying to get some data from a pivot table
When I was using automatic subtotals I was able to use the formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)

When I changed to 'min' subtotals I got a #ref error and tried:
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
(where 'Test1' is a 'Process Skill')

Is it possible to modify this formula to include cell references (i.e. B2,
C2, and D2 as above)

Thanks
Click to expand...
Click to expand...
 
D

Debra Dalgleish

You can substitute the "a" in the formula like this:

'=GETPIVOTDATA($B$15,"Trainee[" & D2 & "] 'Test1' Level['2';Min]")

anna_717717 said:
Hi Shane
I tried this formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)
but got a #ref error. The only explainations I could find for the error was
that when referencing to a specific cell that the formula excel will
automatically generate (=GETPIVOTDATA($B$15,"Trainee[a] 'Test1'
Level['2';Data,Min]") ) is wrong and has to be corrected to
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
Unfortunately I don't understand how I can change this formaula to reference
cells, for example rather than trainee 'a' it would call up whatever value
was is cell D2.

Thanks

:

Apparently I don't follow your problem. A GETPIVOTDATA function will work
regardless of whether the subtotals are SUM or MIN.

And although I don't understand your question, if you have a formula like

=GETPIVOTDATA("Gross Sales",$G$3,"Region","East")

You can change the "Region" or "East" reference to a cell address, even one
that is not in the pivot table. However, to return any resonable result the
entries in those cells must match a field title.

=GETPIVOTDATA("Gross Sales",$G$3,A1,B1)

Is fine, and would return the same result if A1 contained the text Region
and B1 East.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


:

Hi all, hope somebody can help me

I'm trying to get some data from a pivot table
When I was using automatic subtotals I was able to use the formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)

When I changed to 'min' subtotals I got a #ref error and tried:
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
(where 'Test1' is a 'Process Skill')

Is it possible to modify this formula to include cell references (i.e. B2,
C2, and D2 as above)

Thanks
Click to expand...
Click to expand...
Click to expand...
 
A

anna_717717

Thank you very much for your feedback. Works great.
Is it possible to sustitute the 'Test1' and '2' in the formula as well?

Thanks

Debra Dalgleish said:
You can substitute the "a" in the formula like this:

'=GETPIVOTDATA($B$15,"Trainee[" & D2 & "] 'Test1' Level['2';Min]")

anna_717717 said:
Hi Shane
I tried this formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)
but got a #ref error. The only explainations I could find for the error was
that when referencing to a specific cell that the formula excel will
automatically generate (=GETPIVOTDATA($B$15,"Trainee[a] 'Test1'
Level['2';Data,Min]") ) is wrong and has to be corrected to
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
Unfortunately I don't understand how I can change this formaula to reference
cells, for example rather than trainee 'a' it would call up whatever value
was is cell D2.

Thanks

:

Apparently I don't follow your problem. A GETPIVOTDATA function will work
regardless of whether the subtotals are SUM or MIN.

And although I don't understand your question, if you have a formula like

=GETPIVOTDATA("Gross Sales",$G$3,"Region","East")

You can change the "Region" or "East" reference to a cell address, even one
that is not in the pivot table. However, to return any resonable result the
entries in those cells must match a field title.

=GETPIVOTDATA("Gross Sales",$G$3,A1,B1)

Is fine, and would return the same result if A1 contained the text Region
and B1 East.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


:


Hi all, hope somebody can help me

I'm trying to get some data from a pivot table
When I was using automatic subtotals I was able to use the formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)

When I changed to 'min' subtotals I got a #ref error and tried:
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
(where 'Test1' is a 'Process Skill')

Is it possible to modify this formula to include cell references (i.e. B2,
C2, and D2 as above)

Thanks
Click to expand...
Click to expand...


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com
Click to expand...
 
D

Debra Dalgleish

Yes, you can replace those too.

'=GETPIVOTDATA($B$15,"Trainee[" & D2 & "] '" & C2
& "' Level['" & B2 & "';Min]")

anna_717717 said:
Thank you very much for your feedback. Works great.
Is it possible to sustitute the 'Test1' and '2' in the formula as well?

Thanks

:

You can substitute the "a" in the formula like this:

'
Click to expand...
anna_717717 said:
Hi Shane
I tried this formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)
but got a #ref error. The only explainations I could find for the error was
that when referencing to a specific cell that the formula excel will
automatically generate (=GETPIVOTDATA($B$15,"Trainee[a] 'Test1'
Level['2';Data,Min]") ) is wrong and has to be corrected to
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
Unfortunately I don't understand how I can change this formaula to reference
cells, for example rather than trainee 'a' it would call up whatever value
was is cell D2.

Thanks

:



Apparently I don't follow your problem. A GETPIVOTDATA function will work
regardless of whether the subtotals are SUM or MIN.

And although I don't understand your question, if you have a formula like

=GETPIVOTDATA("Gross Sales",$G$3,"Region","East")

You can change the "Region" or "East" reference to a cell address, even one
that is not in the pivot table. However, to return any resonable result the
entries in those cells must match a field title.

=GETPIVOTDATA("Gross Sales",$G$3,A1,B1)

Is fine, and would return the same result if A1 contained the text Region
and B1 East.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


:



Hi all, hope somebody can help me

I'm trying to get some data from a pivot table
When I was using automatic subtotals I was able to use the formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Process Skill",C2,"Level",B2)

When I changed to 'min' subtotals I got a #ref error and tried:
=GETPIVOTDATA($B$15,"Trainee[a] 'Test1' Level['2';Min]")
(where 'Test1' is a 'Process Skill')

Is it possible to modify this formula to include cell references (i.e. B2,
C2, and D2 as above)

Thanks
Click to expand...

--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

getpivotdata custom subtotal cell reference 3
getpivotdata custom subtotal 0
Pivot Table with Data Model Connection - getpivotdata reference issue 0
Pivot Table with Data Model Connection - getpivotdata #REF error 0
Pivot Table GETPIVOTDATA 2
GETPIVOTDATA #REF! error XL2003/2010 1
2007 Excel - GETPIVOTDATA 2
Avoiding auxiliary cells 0

Top