Multiple Cell References in GetPivotData

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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...
 
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...
 
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...
 
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
GETPIVOTDATA and (blank) 0
reference another cell in getpivotdata function 4
Pivot Table with Data Model Connection - getpivotdata #REF error 0
getpivotdata custom subtotal cell reference 3
getpivotdata doesn't like certain numbers?? 2
Is there any way of combining vlookup with getpivotdata? 1
GetPivotData refer to external cell 4

Back
Top