Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Quesiton
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Quesiton
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 On Jun 13, 2:17 pm, Tina wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Quesiton
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... " wrote: 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 On Jun 13, 2:17 pm, Tina wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Quesiton
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 On Jun 13, 3:38 pm, Tina wrote: 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... " wrote: 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 On Jun 13, 2:17 pm, Tina wrote: 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.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Quesiton
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 " wrote: 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 On Jun 13, 3:38 pm, Tina wrote: 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... " wrote: 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 On Jun 13, 2:17 pm, Tina wrote: 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.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Quesiton
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 On Jun 14, 5:24 pm, Tina wrote: 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 " wrote: 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 On Jun 13, 3:38 pm, Tina wrote: 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... " wrote: 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 On Jun 13, 2:17 pm, Tina wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Quesiton
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... " wrote: 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 On Jun 14, 5:24 pm, Tina wrote: 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 " wrote: 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 On Jun 13, 3:38 pm, Tina wrote: 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... " wrote: 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 On Jun 13, 2:17 pm, Tina wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Pivot Table Chart - With Pivot Table Totals | Charts and Charting in Excel | |||
Pivot table keeps dupping to another linked pivot table | Excel Discussion (Misc queries) | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
If..then quesiton. | Excel Worksheet Functions |