Pivot Table as Source Range
 

I just tried using a Pivot table in a Vlookup() and it doesn't seem to work.

=Vlookup(A1,MyPivottableRange,2)

Can someone confirm or inform me otherwise?

TIA

Pivot Table as Source Range
 

Since a pivot table does not repeat the heading values down the columns you
are not going to have a lot of luck using the pivot table as the source for a
vlookup. Perhaps a better explanation of what you are trying to do would
help. A sumproduct formula directed against the source data for the pivot
table might be a better option.
--
HTH...

Jim Thomlinson


"Jim May" wrote:

I just tried using a Pivot table in a Vlookup() and it doesn't seem to work.

=Vlookup(A1,MyPivottableRange,2)

Can someone confirm or inform me otherwise?

TIA

Pivot Table as Source Range
 

Jim - I have on Sheet1 a Pivot table in Range A3:B20

Sum of Contracts $$
State Total
PA 227,112 '=Vlookup(A5, Sheet2!A5:B20, 2) hoped to bring in
227,817
FL 150,000
Mo 125,000

On sheet2 I have a Pivot table in Range A3:B20
Sum of Contracts $$
State Total
PA 227,817
FL 86,662
etc




FA

"Jim Thomlinson" wrote:

Since a pivot table does not repeat the heading values down the columns you
are not going to have a lot of luck using the pivot table as the source for a
vlookup. Perhaps a better explanation of what you are trying to do would
help. A sumproduct formula directed against the source data for the pivot
table might be a better option.
--
HTH...

Jim Thomlinson


"Jim May" wrote:

I just tried using a Pivot table in a Vlookup() and it doesn't seem to work.

=Vlookup(A1,MyPivottableRange,2)

Can someone confirm or inform me otherwise?

TIA

Pivot Table as Source Range
 

although the formula:

=SUMPRODUCT(('S-PT'!$A$5:$A$24=PT!A5)*('S-PT'!$B$5:$B$24))

Works great!!!
Thanks


"Jim May" wrote:

Jim - I have on Sheet1 a Pivot table in Range A3:B20

Sum of Contracts $$
State Total
PA 227,112 '=Vlookup(A5, Sheet2!A5:B20, 2) hoped to bring in
227,817
FL 150,000
Mo 125,000

On sheet2 I have a Pivot table in Range A3:B20
Sum of Contracts $$
State Total
PA 227,817
FL 86,662
etc




FA

"Jim Thomlinson" wrote:

Since a pivot table does not repeat the heading values down the columns you
are not going to have a lot of luck using the pivot table as the source for a
vlookup. Perhaps a better explanation of what you are trying to do would
help. A sumproduct formula directed against the source data for the pivot
table might be a better option.
--
HTH...

Jim Thomlinson


"Jim May" wrote:

I just tried using a Pivot table in a Vlookup() and it doesn't seem to work.

=Vlookup(A1,MyPivottableRange,2)

Can someone confirm or inform me otherwise?

TIA

Pivot Table as Source Range
 

Hi Jim

Take a look at the GetPivotData function.
More help can be found at Debra Dalgleish's site
http://www.contextures.com/xlPivot06.html

--

Regards
Roger Govier

"Jim May" wrote in message
...
I just tried using a Pivot table in a Vlookup() and it doesn't seem to
work.

=Vlookup(A1,MyPivottableRange,2)

Can someone confirm or inform me otherwise?

TIA