| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table - Efficient Source Range | Excel Discussion (Misc queries) | |||
| Using cursor keys to select Pivot Table Source Data Range | Excel Discussion (Misc queries) | |||
| Pivot Table Source Worksheet and Range | Excel Discussion (Misc queries) | |||
| Increasing the Source Data range for an existing Pivot Table | Excel Worksheet Functions | |||
| entry removed from source table remains in pivot table pull down | Excel Worksheet Functions | |||
Linear Mode
