Pivot Table Quesiton

[Guest]

I have a pivot table containing projects by departments with dollars for each
month of the year. To the right of the table, I have formulas to identify
project category based on the project ID in the pivot table rows. Is there a
way to combine these two into one pivot table? Or some other way to combine
the two, so I can sort and report project category?

Thanks much.

 

[krcowen]

Tina

If you have access to the data from which the pivot table is
generated, you can add a column that looks up the category in the ID/
Category table {e.g. =vlookup(ID,table,column)}. Give it a heading of
Category, or something like that, then refresh the Pivot table and the
new field "Category" should be available for you to use in your Pivot
Table.

Good luck.

Ken
Norfolk, Va

 

[Guest]

Thanks Ken, but I do not have access to the data. The data is in a warehouse
with over a million rows, which I have no access to.

Hence the question...

 

[krcowen]

Tina

You may still be able to do what you want. If your initial pivot
table has all the information you need except for the missing Project
Category. In the column immediately to the right of your pivot table,
put in column (with a unique heading) with a lookup formula to pick up
the category from the Project ID-Category table. Then, make another
pivot table from the combination of the pivot table data, and the new
column with the lookup formula.

Ken

 

[Guest]

I tried that Ken, but it shows one of the two new fields I added in the
layout dialog, but does not let me add it.

I selected the whole table and the two columns to the right to do this. But
the data source still points to external.

The error I get is, "the field you are moving can not be placed in the pivot
table area.".

Tina

 

[krcowen]

Tina
It doesn not seem to work like I thought it would. It worked for me
after I converted the original pivot table to values though.
Ken

 

[Guest]

Thanks Ken, that's what I have been doing. Copy/pasting the pivot table,
filling in blank rows using a macro and combining two of these to create a
new pivot table.

One would think MSFT would have thought about this. Duh...