Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
Hi,
I have one data list that includes name of client, city (could be multiple), Category, and Budget. I have created a pivot table that displays my totals per client with breakdown of information that I need, including sub-totals for each client. Problem: I have another "report' that includes total Budget per client. I have tried using DSUM on both the original data table and also on the pivot table, but need to keep referencing the same titles and then changing criteria for each client, so very long and tedious. I tried to use a SUMIF that referenced the pivot table instead, but to be effective, would have to refer to the Subtotals ONLY. If I try to name only subtotals, it doesn't seem to work. I hide the details, select the subtotals and name them, but, of course, this doesn't work when details are redisplayed. Question 1: Which is the best method. Question 2: How can I name only subtotals in a pivot table Any help would be greatly appreciated. -- Thanks! Dee |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
Dee
How about putting Category and budget into the data and use a page for each client? Drag the client field to the top left of the Pivot Design View Regards Peter "dee" wrote: Hi, I have one data list that includes name of client, city (could be multiple), Category, and Budget. I have created a pivot table that displays my totals per client with breakdown of information that I need, including sub-totals for each client. Problem: I have another "report' that includes total Budget per client. I have tried using DSUM on both the original data table and also on the pivot table, but need to keep referencing the same titles and then changing criteria for each client, so very long and tedious. I tried to use a SUMIF that referenced the pivot table instead, but to be effective, would have to refer to the Subtotals ONLY. If I try to name only subtotals, it doesn't seem to work. I hide the details, select the subtotals and name them, but, of course, this doesn't work when details are redisplayed. Question 1: Which is the best method. Question 2: How can I name only subtotals in a pivot table Any help would be greatly appreciated. -- Thanks! Dee |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
You could make a copy of the first pivot table, and show only the data
that you need. Then, use GetPivotData formulas to extract the data. There's information in Excel's Help, and he http://www.contextures.com/xlPivot06.html dee wrote: Hi, I have one data list that includes name of client, city (could be multiple), Category, and Budget. I have created a pivot table that displays my totals per client with breakdown of information that I need, including sub-totals for each client. Problem: I have another "report' that includes total Budget per client. I have tried using DSUM on both the original data table and also on the pivot table, but need to keep referencing the same titles and then changing criteria for each client, so very long and tedious. I tried to use a SUMIF that referenced the pivot table instead, but to be effective, would have to refer to the Subtotals ONLY. If I try to name only subtotals, it doesn't seem to work. I hide the details, select the subtotals and name them, but, of course, this doesn't work when details are redisplayed. Question 1: Which is the best method. Question 2: How can I name only subtotals in a pivot table Any help would be greatly appreciated. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
Hi Debra,
Thanks for your replay. Yes, I had thought of that. I guess the drawback is that it isn't dynamic, from what I can tell. I would have to paste values only, correct? I assume that I cannot name subtotals (only) in a pivot table? I've looked all over (including your excellent site), but can't come up with a definitive answer. -- Thanks! Dee "Debra Dalgleish" wrote: You could make a copy of the first pivot table, and show only the data that you need. Then, use GetPivotData formulas to extract the data. There's information in Excel's Help, and he http://www.contextures.com/xlPivot06.html dee wrote: Hi, I have one data list that includes name of client, city (could be multiple), Category, and Budget. I have created a pivot table that displays my totals per client with breakdown of information that I need, including sub-totals for each client. Problem: I have another "report' that includes total Budget per client. I have tried using DSUM on both the original data table and also on the pivot table, but need to keep referencing the same titles and then changing criteria for each client, so very long and tedious. I tried to use a SUMIF that referenced the pivot table instead, but to be effective, would have to refer to the Subtotals ONLY. If I try to name only subtotals, it doesn't seem to work. I hide the details, select the subtotals and name them, but, of course, this doesn't work when details are redisplayed. Question 1: Which is the best method. Question 2: How can I name only subtotals in a pivot table Any help would be greatly appreciated. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
I meant a regular copy of the pivot table, and when you update one, the
other will update automatically. You don't have to name the subtotals. In the GetPivotData, just refer to the field name, and the data field, and you'll get the subtotal for that item. For example, with a field named Region, and a region name in cell D5: =GETPIVOTDATA("Units",$B$3,"Region",D5) would return the total units for the region. dee wrote: Hi Debra, Thanks for your replay. Yes, I had thought of that. I guess the drawback is that it isn't dynamic, from what I can tell. I would have to paste values only, correct? I assume that I cannot name subtotals (only) in a pivot table? I've looked all over (including your excellent site), but can't come up with a definitive answer. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
OK, you got me back on track! This is what I have done, but I still have one
problem. I copied and pasted the pivot table. I then hid details to view only subtotals I named the subtotal companies and also the subtotal values in order to easily refer to them in a formula On the sheet where I need to add the totals, I used a Sumif that looks up the company name and sums values if it appears in the companies on the pivot table. So far, so good. but... For reasons beyond my control, on the sheet where I am placing the Sumif, the company name may differ slighly from that in the pivot table. For example, ABC may be ABC Company Inc. on the pivot table. I think I need to use a text function within the Sumif to look up the company in the pivot table if it CONTAINS ABC. The problem is that I have many companies, so I want to refer to the cell that contains the company name, not actually type "ABC". This is where I"m having problems. Any help would be greatly appreciated and would allow me to finish this up! Thanks! -- Thanks! Dee "Debra Dalgleish" wrote: I meant a regular copy of the pivot table, and when you update one, the other will update automatically. You don't have to name the subtotals. In the GetPivotData, just refer to the field name, and the data field, and you'll get the subtotal for that item. For example, with a field named Region, and a region name in cell D5: =GETPIVOTDATA("Units",$B$3,"Region",D5) would return the total units for the region. dee wrote: Hi Debra, Thanks for your replay. Yes, I had thought of that. I guess the drawback is that it isn't dynamic, from what I can tell. I would have to paste values only, correct? I assume that I cannot name subtotals (only) in a pivot table? I've looked all over (including your excellent site), but can't come up with a definitive answer. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
You could use wildcard characters with the cell reference, e.g.:
=SUMIF($B$10:$B$200,"*" & K5 & "*",$E$10:$E$200) dee wrote: OK, you got me back on track! This is what I have done, but I still have one problem. I copied and pasted the pivot table. I then hid details to view only subtotals I named the subtotal companies and also the subtotal values in order to easily refer to them in a formula On the sheet where I need to add the totals, I used a Sumif that looks up the company name and sums values if it appears in the companies on the pivot table. So far, so good. but... For reasons beyond my control, on the sheet where I am placing the Sumif, the company name may differ slighly from that in the pivot table. For example, ABC may be ABC Company Inc. on the pivot table. I think I need to use a text function within the Sumif to look up the company in the pivot table if it CONTAINS ABC. The problem is that I have many companies, so I want to refer to the cell that contains the company name, not actually type "ABC". This is where I"m having problems. Any help would be greatly appreciated and would allow me to finish this up! Thanks! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
You are a genius! I tried variations of this, but didn't quite get exactly
what I need... was using things *a2*, for example, but didn't work, of course. Thanks so much! -- Thanks! Dee "Debra Dalgleish" wrote: You could use wildcard characters with the cell reference, e.g.: =SUMIF($B$10:$B$200,"*" & K5 & "*",$E$10:$E$200) dee wrote: OK, you got me back on track! This is what I have done, but I still have one problem. I copied and pasted the pivot table. I then hid details to view only subtotals I named the subtotal companies and also the subtotal values in order to easily refer to them in a formula On the sheet where I need to add the totals, I used a Sumif that looks up the company name and sums values if it appears in the companies on the pivot table. So far, so good. but... For reasons beyond my control, on the sheet where I am placing the Sumif, the company name may differ slighly from that in the pivot table. For example, ABC may be ABC Company Inc. on the pivot table. I think I need to use a text function within the Sumif to look up the company in the pivot table if it CONTAINS ABC. The problem is that I have many companies, so I want to refer to the cell that contains the company name, not actually type "ABC". This is where I"m having problems. Any help would be greatly appreciated and would allow me to finish this up! Thanks! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pls Hlp - Pivot Table or DSum???
You're welcome! Thanks for letting me know that you got it working.
dee wrote: You are a genius! I tried variations of this, but didn't quite get exactly what I need... was using things *a2*, for example, but didn't work, of course. Thanks so much! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
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) | |||
How do I sort pivot table data outside a pivot table | Excel Worksheet Functions | |||
DSUM when database is part of a pivot table | Excel Worksheet Functions | |||
Pivot table and Dsum security | Excel Worksheet Functions | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel |