Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
I have a rather large spreadsheet (~12000 entries) which contains inventory
data. I have many parts which are in multiple location, but I just want a subtotal by part number of the quantities in all locations. I tried subtotal, and the sheet shows the subtotals, but it opens up an outline view or something to that effect, and I want just the summary information on that sheet or be able to copy to another worksheet. But no matter what I do, I either get a grand total entry or all entries, including each location. How do I obtain just the subtotal data where I can perform further functions like VLOOKUP (for another sheet). I tried Pivot Table but it comes back and says something like too many items. I'll readily admit I'm an amateur at subtotals. Can anyone help? Thanks in advance, Dave |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
Check out the DSUM function if you want only a total on a summary sheet. Also you could probably use SUMPRODUCT. -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566153 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
You could use the outlining symbols at the left to hide the details.
Then select the range to copy edit|goto special|visible cells edit|copy and paste to your new location. One more Edit|Replace to remove the " totals" from the key column (if you want). Record a macro when you do this and you'll have some code you can tweak. Dave wrote: I have a rather large spreadsheet (~12000 entries) which contains inventory data. I have many parts which are in multiple location, but I just want a subtotal by part number of the quantities in all locations. I tried subtotal, and the sheet shows the subtotals, but it opens up an outline view or something to that effect, and I want just the summary information on that sheet or be able to copy to another worksheet. But no matter what I do, I either get a grand total entry or all entries, including each location. How do I obtain just the subtotal data where I can perform further functions like VLOOKUP (for another sheet). I tried Pivot Table but it comes back and says something like too many items. I'll readily admit I'm an amateur at subtotals. Can anyone help? Thanks in advance, Dave -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
You can use the Subtotaled Sheet as a basis for getting what you want.
Click on the "2" level to see the Sub-total amounts only. Then Highlight the area (including headers); With the area you want to copy Selected - at the Menu, select Edit, GoTo... Click the Speicail.. button and on the resulting dialog box look on the right side near the bottom and check the Visible Cells Only item. OK out Now do a Copy, go to a new blank sheet and do Edit Paste-Special - Values That should get you there. HTH, Jim "Dave" wrote: I have a rather large spreadsheet (~12000 entries) which contains inventory data. I have many parts which are in multiple location, but I just want a subtotal by part number of the quantities in all locations. I tried subtotal, and the sheet shows the subtotals, but it opens up an outline view or something to that effect, and I want just the summary information on that sheet or be able to copy to another worksheet. But no matter what I do, I either get a grand total entry or all entries, including each location. How do I obtain just the subtotal data where I can perform further functions like VLOOKUP (for another sheet). I tried Pivot Table but it comes back and says something like too many items. I'll readily admit I'm an amateur at subtotals. Can anyone help? Thanks in advance, Dave |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
Thanks Dave, Jim, Excelenator. I managed to muddle through selecting the
level 2 info and pasting it into another sheet then converting to values. That worked for what I need right now, but now I have another problem. I use that data to do a vlookup on another spreadsheet (one of a BOM with multiple instances of a part. I want to summarize the entire quantity per for all occurances of a part to see if I have enough inventory on hand to cover the requirements of that BOM. I can subtotal, the cell formula looks like =SUBTOTAL(9,J35:J49) which is the correct range, but the resulting subtotal is 0, even though the quantites are 0. Is there something I'm missing? Thanks, Dave "Jim May" wrote: You can use the Subtotaled Sheet as a basis for getting what you want. Click on the "2" level to see the Sub-total amounts only. Then Highlight the area (including headers); With the area you want to copy Selected - at the Menu, select Edit, GoTo... Click the Speicail.. button and on the resulting dialog box look on the right side near the bottom and check the Visible Cells Only item. OK out Now do a Copy, go to a new blank sheet and do Edit Paste-Special - Values That should get you there. HTH, Jim "Dave" wrote: I have a rather large spreadsheet (~12000 entries) which contains inventory data. I have many parts which are in multiple location, but I just want a subtotal by part number of the quantities in all locations. I tried subtotal, and the sheet shows the subtotals, but it opens up an outline view or something to that effect, and I want just the summary information on that sheet or be able to copy to another worksheet. But no matter what I do, I either get a grand total entry or all entries, including each location. How do I obtain just the subtotal data where I can perform further functions like VLOOKUP (for another sheet). I tried Pivot Table but it comes back and says something like too many items. I'll readily admit I'm an amateur at subtotals. Can anyone help? Thanks in advance, Dave |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
If your subtotal returns a 0, that means that the
sum is 0 or your values in that range aren't really numbers. They could be Text values that are masquerading as numbers. One way that may convert those text numbers to number numbers is to select an empty cell edit|copy select the offending range (I'd do it before applying data|subtotals) edit|Paste special|check Addition If that doesn't help, maybe... You've copied the values from a web page and your data has some of those HTML non-breaking spaces in the cells. David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()" If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Dave wrote: Thanks Dave, Jim, Excelenator. I managed to muddle through selecting the level 2 info and pasting it into another sheet then converting to values. That worked for what I need right now, but now I have another problem. I use that data to do a vlookup on another spreadsheet (one of a BOM with multiple instances of a part. I want to summarize the entire quantity per for all occurances of a part to see if I have enough inventory on hand to cover the requirements of that BOM. I can subtotal, the cell formula looks like =SUBTOTAL(9,J35:J49) which is the correct range, but the resulting subtotal is 0, even though the quantites are 0. Is there something I'm missing? Thanks, Dave "Jim May" wrote: You can use the Subtotaled Sheet as a basis for getting what you want. Click on the "2" level to see the Sub-total amounts only. Then Highlight the area (including headers); With the area you want to copy Selected - at the Menu, select Edit, GoTo... Click the Speicail.. button and on the resulting dialog box look on the right side near the bottom and check the Visible Cells Only item. OK out Now do a Copy, go to a new blank sheet and do Edit Paste-Special - Values That should get you there. HTH, Jim "Dave" wrote: I have a rather large spreadsheet (~12000 entries) which contains inventory data. I have many parts which are in multiple location, but I just want a subtotal by part number of the quantities in all locations. I tried subtotal, and the sheet shows the subtotals, but it opens up an outline view or something to that effect, and I want just the summary information on that sheet or be able to copy to another worksheet. But no matter what I do, I either get a grand total entry or all entries, including each location. How do I obtain just the subtotal data where I can perform further functions like VLOOKUP (for another sheet). I tried Pivot Table but it comes back and says something like too many items. I'll readily admit I'm an amateur at subtotals. Can anyone help? Thanks in advance, Dave -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals: Nested subtotals below higher subtotal | Excel Discussion (Misc queries) | |||
Presenting Subtotals only | Excel Discussion (Misc queries) | |||
Subtotals adding a description next to the subtotals | Excel Worksheet Functions | |||
How do I copy an outline w/ subtotals & paste just the subtotals | Excel Discussion (Misc queries) | |||
Excel gets subtotals out of order using multiple sorts and subtot. | Excel Discussion (Misc queries) |