Dynamic updating pivot table with additional column data

[cainey1991]

Hi,

in need of some assistance.

I have a pivot table that displays information on servers, which has been pulled from a worksheet. Lets call it this worksheet SERVER INFO
Location code, OS, server role, etc

In another worksheet I have location information. Lets call this LOCATION INFO
Location code, Location function, address, etc

My aim is to have the location function appear next to the location code in my pivot table. So it would now look like:
Location code, location function, OS, server role, etc

A push in the right direction would be greatly appreciated as I've spent a good amount of my day trawling the web for a solution.

If you need any additional information please let me know

 

[Fazza]

welcome

save your data file/s

IIRC these are the steps - from a new file start the pivot table wizard (ALT-D-P) choosing the external data option at the first step. 'Get Data', Excel files, OK, select your data file (or one of them if you have two), if you get a message about no visible tables OK to acknowledge it then via 'options' select 'system tables' to see the worksheet/s listed. pick a worksheet & the fields you want. (In fact you can pick both worksheets here if they are in the one file. but I'll assume you just pick one data source) continue to the end and at the final step take the option to edit in MS Query. From this GUI add the second table (which is either from the same file or you have an extra step to identify the data file if it is different) and you should see both tables appearing at the top of the GUI. Drag & join between the common field to set up a join between the tables. (You might be OK with the default INNER JOIN but if not you can right click & edit the join type. MS Query help discusses.) Add whatever fields from the second table you want in the dataset - & you will see these on the screen. When OK hit the 'open door' icon to exit MS Query & then complete the pivot table

If you google there should be plenty of examples, including hundreds of similar threads where I've posted on Mr Excel

regards

 

[cainey1991]

Thanks for the through response. I'll give this a go now and update you on my progress.

 

[cainey1991]

Thanks for that Fazza, worked a charm!

I do have one hiccup though...
For some reason I have server locations that exist in one worksheet but not in the other.
Is there a way to still show those that exist even if they dont in the other as at the moment my new pivot table is only showing location codes that exist in both 'tables'

Again, thanks for all your help so far!!

 

[cainey1991]

Never mind I figured it out.
For those interested to change the link type or the query used in general :
Options in the pivot table
change data source > connection properties
A window will appear. Click the Definition tab
At the bottom click Edit Query.

 

[Fazza]

I didn't spell that out, sorry, when I wrote above, "Drag & join between the common field to set up a join between the tables. (You might be OK with the default INNER JOIN but if not you can right click & edit the join type. MS Query help discusses.)"

You're spot on, only the codes common to both tables appear with the default table join. And what you want is an outer join, which is easily done. Can you right click from the join and select option 2?

If you're in MS Query and ask for help on joins you can read about it. I'll have a quick google for the same data at a microsoft site.

PS Some info on joins http://support.microsoft.com/kb/136699

 

[Fazza]

Never mind I figured it out.
For those interested to change the link type or the query used in general :
Options in the pivot table
change data source > connection properties
A window will appear. Click the Definition tab
At the bottom click Edit Query.

Your post arrived when I was writing my message. For those interested, what you describe is for Excel 2007 & newer, not earlier versions. Well done