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,"Pr ocess 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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"anna_717717" wrote:

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,"Pr ocess 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

Hi Shane
I tried this formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Pr ocess 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" wrote:

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" wrote:

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,"Pr ocess 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

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

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

anna_717717 wrote:
Hi Shane
I tried this formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Pr ocess 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" wrote:


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" wrote:


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



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

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" wrote:

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

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

anna_717717 wrote:
Hi Shane
I tried this formula:
=GETPIVOTDATA("assessment2",$B$15,"Trainee",D2,"Pr ocess 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" wrote:


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" wrote:


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



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

Yes, you can replace those too.

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

anna_717717 wrote:
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" wrote:


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

'



anna_717717 wrote:

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" wrote:



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" wrote:



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",D 2,"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


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





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