Multiple Cell References in GetPivotData

S

Sarah (OGI)

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?
 
D

Debra Dalgleish

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)
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?
Click to expand...
 
S

Sarah (OGI)

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 said:
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)
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?
Click to expand...
Click to expand...
 
S

Sarah (OGI)

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 said:
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)
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?
Click to expand...
Click to expand...
 
D

Debra Dalgleish

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.
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?

:

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)
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?
Click to expand...
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

Pivot Table with Data Model Connection - getpivotdata reference issue 0
Pivot Table with Data Model Connection - getpivotdata #REF error 0
Dates In Excel 5
GETPIVOTDATA and (blank) 0
ISERROR on GETPIVOTDATA 1
Pivot Table GETPIVOTDATA 2
GETPIVOTDATA and Date-type field (Excel 2007) 0
getpivotdata doesn't like certain numbers?? 2

Top