Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculated Field - vLookup
Getting error message "References, names and arrays are not supported in pivot table formulas"
When entering vLookup in a pivot table calculated field. Example: Name: TblLu Formula: =vlookup(Re_Num,newrates,34,false) Please help. Thanks, Ken |
#2
|
|||
|
|||
Quote:
Is "Re_Num" in your workbook a single cell or a range of cells? |
#3
|
|||
|
|||
Quote:
A single cell. All I am trying to do using an Item Id is read a table and display info from that table. Re_Num is simply the item Id. Thanks, Ken |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculated Field - vLookup
Ken,
It is my understanding that PivotTable Calculated Fields cannot use any formula that requires a range as one of its arguments. There are a few ways to work around it. Here are a couple of suggestions: 1. Use the CHOOSE function. If you have 29 or fewer lookup items, you could use the CHOOSE function to select the appropriate item from the list. 2. Add a column to your source data. If you are able, simply add a column to the table that feeds your PivotTable and use the VLOOKUP within that column instead. Then, that new column will be available to add as a field to your PivotTable. 3. Use the GetPivotData function. To use this option, simply use the VLOOKUP in a range of cells outside of the PivotTable and use GetPivotData to pull in any data needed from the PT. None of these may be ideal for your needs, but hopefully one of them will work out. Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table, IF function, calculated item versus calculated field | Excel Discussion (Misc queries) | |||
I want to create a calculated item based on a calculated field | Excel Discussion (Misc queries) | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Formulas Calculated Item / Calculated Field | Excel Programming | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) |