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
|