Can I use VLOOKUP in the pivot fields to pull data from a pivot table?

[ericrichard25]

Hello. In the ‘Positions Pivot’ tab, in column B, I want to display the industry of each stock listed in column A. The industry data is to be pulled from the pivot table in the ’Positions’ tab (column P). Just want to point out that this industry data is pulled using VLOOKUP from the ‘Summarizer Lite’ tab (column B). I am a bit new to pivot tables so I was hoping for some suggestions on how to do this. Thanks.

[dflak]

You can solve this by overlaying the pivot table with a named dynamic range and use that as the lookup source for the VLOOKUP.

The following makes a couple of assumptions. Te first cell with data in the pivot table is A6 everything above that is either header or data. Likewise there are 19 columns returned in the pivot table.

Here is information on named dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges

In this case, create a name called Lookup_Data with the following reference =OFFSET('Summarizer Lite'!$A$6,0,0,COUNTA('Summarizer Lite'!$A:$A)-4,19)

What this name means is:
Start in Cell A6
Go down 0 rows
Go right 0 Columns (so we are still in A6)
give me a range COUNTA(A:A)-4 rows deep and 19 rows wide

The reason for the -4 is that we don't want to count the header and other information you have above the data.

Then you can use VLOOKUP with Lookup_Data as the table you go to to look up the data. E.g. =VLOOKUP(A2,Lookup_data,2,False)

[ericrichard25]

Thanks for the reply dflak. I think I understand how to create the dynamic named range that overlaps with the pivot table, but where do I type the VLOOKUP formula =VLOOKUP(A2,Lookup_data,2,False)? When I try and type it in column B of the pivot fields, I get an error message saying you can't change this part of the pivot table.

[dflak]

My apologies, I did not understand the requirement. I thought you wanted to use the results of the pivot table to do further analysis elsewhere.

Also you don't need the dynamic range you have the data in a table which is even better.

The solution is a lot simpler than my misunderstanding of the requirement led me to believe.

I took the positions data and made a table out of it. It's just easier to work with tables. Then I made a pivot table of it on Sheet1. Instead of putting the Industry as data, I put it as an additional row in the pivot table.

Then right click on the symbol, select Field Settings. Click on None for subtotals and then go to the layout and print tab and click on Show item labels in tabular form.

[ericrichard25]

Nice. It did seem like something relatively simple, but I'm just starting out. Thanks so much for your help dflak.