Copy only visible cells after applying "Subtotals"

Hello,

in an XL-list I need to copy only a block of subtotals to another area, 
without the detail rows in between. I believe there once was a command like 
"paste only visible cells" (or similar), but I can't find a solution. 
Anybody who knows how to do this ?

Thank you in advance,

H.G. Lamy 


0
H
11/16/2009 6:04:59 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1288 Views

Similar Articles

[PageSpeed] 40

You can copy the visible cells.

Show just the subtotals
select the range to copy
Edit|goto (or F5 or ctrl-g)|special|visible cells only

Then edit|copy
and paste where you want.



"H.G. Lamy" wrote:
> 
> Hello,
> 
> in an XL-list I need to copy only a block of subtotals to another area,
> without the detail rows in between. I believe there once was a command like
> "paste only visible cells" (or similar), but I can't find a solution.
> Anybody who knows how to do this ?
> 
> Thank you in advance,
> 
> H.G. Lamy

-- 

Dave Peterson
0
Dave
11/16/2009 6:29:05 PM
Dave,
that was it, many thanks!
Regards,

H.G. Lamy

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4B0199F1.EBAD3361@verizonXSPAM.net...
> You can copy the visible cells.
>
> Show just the subtotals
> select the range to copy
> Edit|goto (or F5 or ctrl-g)|special|visible cells only
>
> Then edit|copy
> and paste where you want.
>
>
>
> "H.G. Lamy" wrote:
>>
>> Hello,
>>
>> in an XL-list I need to copy only a block of subtotals to another area,
>> without the detail rows in between. I believe there once was a command 
>> like
>> "paste only visible cells" (or similar), but I can't find a solution.
>> Anybody who knows how to do this ?
>>
>> Thank you in advance,
>>
>> H.G. Lamy
>
> -- 
>
> Dave Peterson 


0
H
11/16/2009 6:38:49 PM
Reply:

Similar Artilces:

Pivot table subtotals 100%
This is what I want: Apples ...

Vlookup & Subtotals
I have a subtotaled sheet from an external data source with only tw columns( part#, qty) on another sheet I have my vlookup statement whic has the part# column, how can I get the vlookup to ignore the wor total on the subtotal page. The statement I am using is vlookup(a1,sheet4!A:B,2,false) This worked fine until I chose to go the external data route. Thanks in advance for any idea -- Message posted from http://www.ExcelForum.com Are you sure you want to ignore the word Total? It kind of sounds like you want it to only retrieve the data from the total row???? =vlookup(a1&" total&...

Ability to suppress subtotals in FRx Drilldown Viewer
Customer would like the ability to suppress subtotals in the transaction detail level of the Drilldown Viewer. If there is only one line of detail, you end up having two lines (one for the detail and one for the subtotal) with the same number. Customer does not want to see the subtotal in this case. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Micr...

Problem with 2 levels of subtotals
I have a weird situation here. I'm trying to create 2 levels of subtotals from the spreadsheet. I have no problem creating 2 levels of subtotals using Excel 2000. However, with Excel 2003 I keep on getting 'mix up' answers on the second level subtotal. The first level of subtotal works fine until I try to create the second level of subtotals. Can somebody please advice whether is there a way around this problem in Excel 2003? Thanks in advance I'm not sure what mixed up answers are, but there are a couple of things that I can think of that can go astray. #1. The ...

Graphing Subtotals
I have an Excel sheet with many rows with subtotals in between. I wish to graph the subtotals without including the data that created the subtotals. I have been unable to exclude the underlying data from the graph. I wish the graph to show the subtotals only. Help please, Henry If I understand your problem correctly, when selecting the range for the chart, first select the titles and then hold the Ctrl key down while selecting the data series. If you have more than one data series then continue to hold the Ctrl key and select the next data series. Regards, OssieMac "Henry" w...

Export Reports Subtotals
Hi Is there any way to export report's subtotals to Excel? Thanks, Dan D wrote: >Is there any way to export report's subtotals to Excel? Exporting a report is not a good idea. Instead, you should create a query (similar to the report's record source query?) that calculates the subtotals. Then export the query to Excel. -- Marsh MVP [MS Access] Thanks Marsh! How do I do that in the below SQL: thans! SELECT v1frJnlIdNewFile.Key1, Left([key1],5) AS OrgUnit, Mid([key1],7,3) AS Ccy, Mid([key1],11,7) AS Acct, Mid([key1],19,3) AS SubAcct, Mid([key1],23,4) AS Prod, Mid([...

subtotals in smart list
Hi everyone, Is it possible to have totals in smart list ?? if yes ... is it a 3rd party tool or any trick or tips ?? or best of all any code samples !. Thanks Ashfaq Either AnyView from Accountable Software or SmartList Builder from eOne Solutions. "Ashfaq Ali" <ashfaq@cyberdude.com> wrote in message news:%23XL%23UtcFFHA.3728@TK2MSFTNGP14.phx.gbl... > Hi everyone, > > Is it possible to have totals in smart list ?? if yes ... is it a 3rd > party > tool or any trick or tips ?? or best of all any code samples !. > > Thanks > Ashfaq > > I...

Eliminating Pivot Table Subtotals
I've created a pivot table and I want to eliminate the subtotal (I don't want to hide all the subtotal rows). Any ideas? e.g. Jan Item 1 2002 1000 2003 1000 2004 1000 2005 1000 Item 1 subtotal 4000 <-- eliminate this Double-click on the Item field button For Subtotals, select None Click OK cailotto@sbcglobal.net wrote: > I've created a pivot table and I want to eliminate the subtotal (I > don't want to hide all the subtotal rows). Any ideas? > > e.g. > Jan > Item 1 2002 1...

keeping subtotals accurate to reflect sorting...
Hi, not sure how to present my question briefly.... I've created an inventory sheet for our restaurants with the following columns: category, description, vendor, item #, price, etc etc etc. At the bottom of the 250 some odd line items I have everything subtotaled by category (all beverages, dairy, spices, produce, etc) and created those subtotals by typing "=sum(r4:r21,u4:21)" as beverages are in rows 4-21 and inventory calculation results are found in columns R and U. and I repleated this for all categories... this is fine until people start sorting. when sortin...

Subtotals in Pivot Tables
I've got a spreadsheet with a number of columns and I could do with putting a subtotal into a column in the middle of the data. Currently the columns are: Charged - Location - Size - Item Every line in my source data has a value in the Size column which is the same for each Location so that if I delete a line I don't end up without a Size for a Location.... but I want to be able to total the Size for each Charged and currently all I can do is get it to count every Size for every Item which gives me the wrong answer. So for instance if I've got data of: Charged - Customer A ...

% of column on SUBTOTALS in a pivot table
I know how to use the % to column feature from the Pivot table options, but I also need to show a percentage of each subtotal. This needs to go in the Pivot table, as the data gets refreshed all the time. I can do a formula outside of the Pivot table, but need it to be as part ot the Pivot. I really would appreciate help Thanks Elsa Go to field settings, select show data as % of total Regards, Peo Sjoblom "Elsa" wrote: > I know how to use the % to column feature from the Pivot table options, but I > also need to show a percentage of each subtotal. This needs to go...

How do I copy/paste subtotals without all the extra data?
I am trying to either do a vlookup or copy and paste the subtotals without getting all the data in the hidden rows? number of ways you can do this...i had the same question earlier. 1. highlight the subtotals results that you want to copy. hit F5 special and "visible cells only" then ctrl C, which sould highligh with white lines the subtotals you want to copy. then ctrl V t wherever you want. it'll get rid of all the data in the hidden row and leave only the subtotal results. 2. copy and paste from Excel into Word and back again. That should d the trick. 3. Copy a...

Pivot table subtotals before detail
I'm new to pivot tables. Is there a way to have the subtotals appear before the detail listing? Right-click the field button Choose Field Settings Click the Layout button Select 'Show items in outline form' Check the box for 'Display subtotals at top of group' Click OK, click OK Jeanmarie wrote: > I'm new to pivot tables. Is there a way to have the subtotals appear before > the detail listing? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Got it, thanks! "Debra Dalgleish" wrote: > Right-cli...

Lists, Excel 2003, automatic subtotals
I have created a List using Excel 2003. I have sorted the list on the selected field. Instructions say to select a cell outside the list and select Data, Subtotal. However, the Subtotal is grayed. Therefore, the Subtotal dialog box is not available. I have done this in the past with Excel 2002; then one had to be in the list for the Subtotal dialog box to be available. However, if I go inside the List, it doesn't work either. Any help would be appreciated. Hi Suomi, > I have created a List using Excel 2003. I have sorted the > list on the selected field. Ins...

Subtotals #9
I want to paste the results of a subtotal into another worksheet in a book. How can I paste just the subtotaled info wothout all of the drilldown info it is based on. I have tried Paste/Values only etc. No Luck. The only thing I have found that works is pasting to a text file, then copying and pasting that back into Exel. Is there an easier way? I do this about 30 times a day and it seems like it should be easier!! Thanks, Tom I have 2002 XP I have no problems with Paste values with a subtotal. What do you use Do you have any problems with paste value for any other function? "to...

Pivote Table
This issue can be seen quickly by downloading a sample excel file I pu together for the question (no macros) Full explanation is included in the file: http://www.ctc-dev.com/excel/pivottablesample.xls I am running into issues because I am having to use an average instea of a sum in one of the column fields in a pivot table. Because of thi average field, all the subtotals and percentage columns based on thi field are all thrown off +---------------------------------------------------------------- | Attachment filename: pivottablesample.xls |Download attachment...

2 subtotals from one report
Access 2000. I have an employee database with employee hours worked, miles driven, expenses,date etc. I need a report for payroll reporting purposes. The employee pay period is every two weeks. Sun. of the first week to Sat. of the next week (14 days). I can very easily create a report showing each day worked and a Sum of hours, expenses etc. using a between statement in the query showing the correct date range of the pay period. I have been asked to create a report that will have a subtotal for the first week, let say 42.1 hours, and a subtotal for the second week lets say 40.0 h...

Copy and Paste Subtotals only.
How do you copy and paste subtotals only? I use the Subtotal feature in Excel to determine the maximum values for different entities in Excel. I reduce the list to show the subtotals only and I want to copy and paste these values in a separate spreadsheet. However, when I copy and paste the maximum values, Excel pastes all of the data. Thanks, Jim -- Jim15 ------------------------------------------------------------------------ Jim15's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26300 View this thread: http://www.excelforum.com/showthread.php?threadid=4...

How to check whether subtotals are on a worksheet
Hi, does anybody know a way of checking whether subtotals have been added to a worksheet using a macro?? Cheers, Alan How about just looking for "=subtotal("? Record a macro when you do it manually (edit|find) AlanAylett wrote: > > Hi, > > does anybody know a way of checking whether subtotals have been added > to a worksheet using a macro?? > > Cheers, Alan -- Dave Peterson ec35720@msn.com ...

When using subtotals in Excel, how do you bold the dollar amount?
I am trying to create a report using Excel that I use subtotals for. I sort it by Department and I always get the department name bolded, but have to manually bold the corresponding dollar amount. Any suggestions? Here is one way... After creating all the subtotals, collapse the outline so that only the subtotal rows are visible. Select all the data, then press Alt-: (Alt and a colon) to select only the visible cells within the whole range you just selected. Another way is to press F5, then Special >> Visible cells only >> OK. Now apply Bold or any other formatti...

Displaying a count as a percentage within subtotals in a pivot tab
Excel 2007 I have a count field in a pivot table column that I want to display as a percentage. If I have no subtotals, the option "% of column" in Value Field settings works great. However if I add subtotals to the column and want the percentages to calculate WITHIN SUBTOTALS as opposed to the whole column, I can't find an option in the Value Field settings that does that (I tried "% Of" to no avail). Is this even doable? -- John Shahan Excel 2007 Pivot Table Subtotals with % of column Use helper column or macro: http://www.mediafire.com/file/iz4wwuihm1b/...

subtotals to a different page.
Is it easily doable to have the subtotals from sheet 1 calculate (show?) on sheet 2? I have used the Data->Subtotals to get what I want on the sheet but when I want to copy only the subtotals to another sheet, I get all the cells. I have been exporting the info to Access and then doing a selective import and filter and then exporting it back to Excel. Very round about and clumsy. Thanks you very much for looking and any answers. Lee Do your data|subtotals use the outlining symbols to the left to hide all the details select the range to copy Edit|goto|special|visible cells only edit...

Creating a chart with subtotals
I am trying to create a chart from a data list that has been subtotalled using the Excel DATA/SUBTOTALS feature. The main problem I have is that when I do this, my list of items all have "COUNT" after the name. This makes the chart look scruffy. Is there any way to overcome this? Thanks in advance Karl ...

How do I create a chart from subtotals in Excel
I am trying to create a chart just picking up the subtotal figures, but it seems to get confused and produces a nonsensical chart. On Wed, 21 Nov 2007, in microsoft.public.excel.charting, Marion <Marion@discussions.microsoft.com> said: >I am trying to create a chart just picking up the subtotal figures, but it >seems to get confused and produces a nonsensical chart. Use a pivot table. -- Del Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. Did you create the subtotals u...

Subtotals keys on second row instead of first
Working in Excel 2000, trying to subtotal data. Subtotals have worked every time before, but now where it says "At every change in..." it gives the second row of data instead of the first. Any ideas what would cause this? David Soliday dsoliday@sarcom.com ...