getpivotdata custom subtotal

  • Thread starter Thread starter anna_717717
  • Start date Start date
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
 
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.
 
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...
 
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...
 
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...
 
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...
 
Back
Top