Pivot table and v lookup - stumped.

chuf

Well-known Member
Joined
Jun 12, 2006
Messages
619
I've read through old threads for the past hour or so and can't find an answer to this, appreciate any help please.

I have a pivot table which has a number of fields.

One field in the pivot table contains cc codes, four figure text with trailing spaces, and within the same spreadsheet, different worksheet, i have a list which gives me the cc code and the name of that code.

I'd like an additional column in the pivot table which uses a vlookup on the cc code and gives me the name.. i'd them like to be able to sort in the pivot table on the name and group them.

This must be possible, mustn't it ? ... but i'm stumped.

Any questions, or clarification please let me know.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Thanks for the reply.

I read about that but, maybe wrongly, i thought that was to get information out of the pivot table and not into it ?

I'll re-read, apologies if i've misunderstood.

best wishes, Chuf
 
Upvote 0
...i thought that was to get information out of the pivot table and not into it ?

But that's quite right. Also, if you want certain information included into a pivot table, then you'll need to integrate the information in the source data from which you want to assemble that pivot table, not into the resulting pivot table.

I'll re-read, apologies if i've misunderstood.

best wishes, Chuf

I was obviously misreading...
 
Upvote 0
Thanks, i thought it was going mad .. i've got about 3 xl for idiots books in front of me ( i need more than 3 :) ) and have googled, xl helped and so on... but nothing.

so i'm glad its not possible, if you get my drift... back to the drawing board.

have a nice day. Chuf
 
Upvote 0
Hi, Chuf.

Not sure why you write it is not possible: it is possible. As Aladin emphasised, put the information in the source data.

When you have two (or more, can be hundreds or even more) separate sources like this, they need to be joined together using SQL. So either create the pivot table by (1) selecting external data as the source at the first step of the pivot table wizard, or, (2) using external data via menu path data, import external data, new database query. Either way you can reach somewhere to enter the SQL that will tell Excel that you want to take some fields from one source and another(s) from a second source (or more), and also specify the criteria of how the tables are joined.

For example, if you use named ranges for the data, tblMainData and tblCC, then something like below in the SQL (using your field headers of course, I've just put something here to give you the idea).

HTH, Fazza

Code:
SELECT MD.*, C.CC_DESC
FROM tblMainData MD, tblCC C
WHERE MD.CC_code = C.CC_code
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top