Multiple Cell References in GetPivotData
 

I have a pivot table on one worksheet and in another I'm trying to use the
getpivotdata function to pull out data based on cell references.

Worksheet: CNM (A4:E10)
Headings: Client Ref / Visit Date / Company / Month 1 / Month 2
Detail: 1234 / 01/03/2007 / ABC / [monetary data from pivot table] /
[monetary data from pivot table]

Worksheet: Pivot (A3:J198)
Row Fields: Client Ref / Company / Company
Column Fields: -3 / -2 / -1 / 9 / 10 / 11 / Grand Total
Data Items: the monetary data I am trying to extract.

I've tried to use the advice given by Debra Dalgleish on her website
(www.contextures.com/xlPivot06.html), but, I cannot get it work. (I am using
Excel 2000.)

I've entered:
=GETPIVOTDATA(Pivot!$A$4,"-3 "&C6 & " " &C6 & " " &A6)

where the '-3' relates to one of the columns of data from the pivot table
that I want to extract from. If it helps, my worksheets are organised as
follows:

Basically, I need to extract the value in column '-3' which matches the
company name, the company name (again) and the client ref.

Any ideas?

Multiple Cell References in GetPivotData
 

What's the field name for the column headings? The numbers, -3, -2,
etc., look like field items, not field names.
In the formula, use a field name, space, item name. For example, if the
field names are MonthDiff and Client Ref:

=GETPIVOTDATA(Pivot!$A$4,"MonthDiff -3 'Client Ref' 1234")

To use cell references, where the MonthDiff item is in A6, and the
Client Ref item is in C6:

=GETPIVOTDATA(Pivot!$A$4,"MonthDiff " & A6 & " 'Client Ref' " & C6)

Sarah (OGI) wrote:
I have a pivot table on one worksheet and in another I'm trying to use the
getpivotdata function to pull out data based on cell references.

Worksheet: CNM (A4:E10)
Headings: Client Ref / Visit Date / Company / Month 1 / Month 2
Detail: 1234 / 01/03/2007 / ABC / [monetary data from pivot table] /
[monetary data from pivot table]

Worksheet: Pivot (A3:J198)
Row Fields: Client Ref / Company / Company
Column Fields: -3 / -2 / -1 / 9 / 10 / 11 / Grand Total
Data Items: the monetary data I am trying to extract.

I've tried to use the advice given by Debra Dalgleish on her website
(www.contextures.com/xlPivot06.html), but, I cannot get it work. (I am using
Excel 2000.)

I've entered:
=GETPIVOTDATA(Pivot!$A$4,"-3 "&C6 & " " &C6 & " " &A6)

where the '-3' relates to one of the columns of data from the pivot table
that I want to extract from. If it helps, my worksheets are organised as
follows:

Basically, I need to extract the value in column '-3' which matches the
company name, the company name (again) and the client ref.

Any ideas?


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

Multiple Cell References in GetPivotData
 

Thanks for the help.

The numbers you questioned are in fact field names. They're taken from a
query in Access and they each correspond to a month, either months 1,2,3
after a given date and months 9,10,11 before the same given date.

I managed to get this to work, although I did change the source data
slightly so that where I had "Client Ref / Company / Company" in my pivot
table, I concatenated them in the Access query so it appeared in the pivot as
"Client Ref / CompanyCompany".

For reference, this is the formula I then entered into Excel:
=GETPIVOTDATA(Pivot!$A$4,"11 " &$C6&$C6 &" " &$A6)

Thanks again for the help!

"Debra Dalgleish" wrote:

What's the field name for the column headings? The numbers, -3, -2,
etc., look like field items, not field names.
In the formula, use a field name, space, item name. For example, if the
field names are MonthDiff and Client Ref:

=GETPIVOTDATA(Pivot!$A$4,"MonthDiff -3 'Client Ref' 1234")

To use cell references, where the MonthDiff item is in A6, and the
Client Ref item is in C6:

=GETPIVOTDATA(Pivot!$A$4,"MonthDiff " & A6 & " 'Client Ref' " & C6)

Sarah (OGI) wrote:
I have a pivot table on one worksheet and in another I'm trying to use the
getpivotdata function to pull out data based on cell references.

Worksheet: CNM (A4:E10)
Headings: Client Ref / Visit Date / Company / Month 1 / Month 2
Detail: 1234 / 01/03/2007 / ABC / [monetary data from pivot table] /
[monetary data from pivot table]

Worksheet: Pivot (A3:J198)
Row Fields: Client Ref / Company / Company
Column Fields: -3 / -2 / -1 / 9 / 10 / 11 / Grand Total
Data Items: the monetary data I am trying to extract.

I've tried to use the advice given by Debra Dalgleish on her website
(www.contextures.com/xlPivot06.html), but, I cannot get it work. (I am using
Excel 2000.)

I've entered:
=GETPIVOTDATA(Pivot!$A$4,"-3 "&C6 & " " &C6 & " " &A6)

where the '-3' relates to one of the columns of data from the pivot table
that I want to extract from. If it helps, my worksheets are organised as
follows:

Basically, I need to extract the value in column '-3' which matches the
company name, the company name (again) and the client ref.

Any ideas?


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

Multiple Cell References in GetPivotData
 

Debra

Is there a limit as to how many cell references can be used in the
getpivotdata function? I know that you can only have 7 IF statements in a
formula - I was just wondering if the same applies to getpivotdata?

"Debra Dalgleish" wrote:

What's the field name for the column headings? The numbers, -3, -2,
etc., look like field items, not field names.
In the formula, use a field name, space, item name. For example, if the
field names are MonthDiff and Client Ref:

=GETPIVOTDATA(Pivot!$A$4,"MonthDiff -3 'Client Ref' 1234")

To use cell references, where the MonthDiff item is in A6, and the
Client Ref item is in C6:

=GETPIVOTDATA(Pivot!$A$4,"MonthDiff " & A6 & " 'Client Ref' " & C6)

Sarah (OGI) wrote:
I have a pivot table on one worksheet and in another I'm trying to use the
getpivotdata function to pull out data based on cell references.

Worksheet: CNM (A4:E10)
Headings: Client Ref / Visit Date / Company / Month 1 / Month 2
Detail: 1234 / 01/03/2007 / ABC / [monetary data from pivot table] /
[monetary data from pivot table]

Worksheet: Pivot (A3:J198)
Row Fields: Client Ref / Company / Company
Column Fields: -3 / -2 / -1 / 9 / 10 / 11 / Grand Total
Data Items: the monetary data I am trying to extract.

I've tried to use the advice given by Debra Dalgleish on her website
(www.contextures.com/xlPivot06.html), but, I cannot get it work. (I am using
Excel 2000.)

I've entered:
=GETPIVOTDATA(Pivot!$A$4,"-3 "&C6 & " " &C6 & " " &A6)

where the '-3' relates to one of the columns of data from the pivot table
that I want to extract from. If it helps, my worksheets are organised as
follows:

Basically, I need to extract the value in column '-3' which matches the
company name, the company name (again) and the client ref.

Any ideas?


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

Multiple Cell References in GetPivotData
 

I never hit the limit in Excel 2000. In Excel 2003 it's 14 field names
and items. In Excel 2000, you probably won't create a pivot table with
that many fields.

Sarah (OGI) wrote:
Debra

Is there a limit as to how many cell references can be used in the
getpivotdata function? I know that you can only have 7 IF statements in a
formula - I was just wondering if the same applies to getpivotdata?

"Debra Dalgleish" wrote:


What's the field name for the column headings? The numbers, -3, -2,
etc., look like field items, not field names.
In the formula, use a field name, space, item name. For example, if the
field names are MonthDiff and Client Ref:

=GETPIVOTDATA(Pivot!$A$4,"MonthDiff -3 'Client Ref' 1234")

To use cell references, where the MonthDiff item is in A6, and the
Client Ref item is in C6:

=GETPIVOTDATA(Pivot!$A$4,"MonthDiff " & A6 & " 'Client Ref' " & C6)

Sarah (OGI) wrote:

I have a pivot table on one worksheet and in another I'm trying to use the
getpivotdata function to pull out data based on cell references.

Worksheet: CNM (A4:E10)
Headings: Client Ref / Visit Date / Company / Month 1 / Month 2
Detail: 1234 / 01/03/2007 / ABC / [monetary data from pivot table] /
[monetary data from pivot table]

Worksheet: Pivot (A3:J198)
Row Fields: Client Ref / Company / Company
Column Fields: -3 / -2 / -1 / 9 / 10 / 11 / Grand Total
Data Items: the monetary data I am trying to extract.

I've tried to use the advice given by Debra Dalgleish on her website
(www.contextures.com/xlPivot06.html), but, I cannot get it work. (I am using
Excel 2000.)

I've entered:
=GETPIVOTDATA(Pivot!$A$4,"-3 "&C6 & " " &C6 & " " &A6)

where the '-3' relates to one of the columns of data from the pivot table
that I want to extract from. If it helps, my worksheets are organised as
follows:

Basically, I need to extract the value in column '-3' which matches the
company name, the company name (again) and the client ref.

Any ideas?


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





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