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
1335 Views

Similar Articles

[PageSpeed] 4

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:

Problem with nested subtotals, placing secondary subtotals BELOW .
I some but not all spreadsheets, my nested subtotal (slsperson) is placed below the primary subtotal (branch). It has only happened to me since upgrading to Excel 2003. I sort by Branch, slsperson, customer. Then Primary subtotal is branch (checks on replace current subtotals and summary below) and all is fine. Then when I do subtotal on Slsperson it will place (above items unchecked) the slsperson subtotal BELOW the branch. And also the levels (1,2,3) are not displaying properly. Does anyone have a suggestion for me? maybe... Multilevel subtotals are in the wrong position in Excel...

Duplicates and subtotals
Each week we receive a file from our payroll system, approx 6000 rows. Contains General Ledger data by employee and GL account with amounts. We use this file to post to our GL system. I'd like to find a way in Excel to subtotal by row containing unique data. Emp Co Accnt date amount 1234 10 4000 10/17/03 100.00 1234 10 4000 10/17/03 5.00 I would like to see, instead of two lines of detail, 1234 10 4000 10/17/03 105.00 Anyone had the opportunity to do this? Thanks in advance Deanna I'd insert a new column A and use a helper formula: (as...

Applying formulas only to the subtotals of a data list
I have a large data list with subtotals (in fact with nested subtotals). I have collapsed the list so that only the subtotals are showing. Next I set up a few columns to the right of the data list with the intent of applying certain calculations, again ONLY to the subtotals. Here lieth the problem: when I set up the formulas for the first subtotal and attempt to simply drag it down, Excel obviously assumes that the copied formula applies to the next row of the full-blown, uncollapsed data list. Is there any way to accomplish the task without having to manually create a separate table ...

Division by zero in automated subtotals
What is the easiest way to division by zero errors that occur when using automated subtotals to display averages? Look for a count of numbers to be bigger than 0. =IF(SUBTOTAL(2,A2:A10)=0,"",SUBTOTAL(1,A2:A10)) Neil Berkowitz wrote: > > What is the easiest way to division by zero errors that occur when using > automated subtotals to display averages? -- Dave Peterson ...

subtotals #3
I would like to create a macro (or vba function) that subtotals by user then by type. I have two fields that I would like to total, amount and reimbursement amount. I would like the macro to sort and subtotal on type and then provide a grand total by user. I have three different types (P, C & R). I tried using the sort and subtotal functions in excel (2003) but have not had any success. Can someone please point me in the right direction? kind regards, Since you provide little info, If I understand you don't need to subtotal. Try this idea =sumproduct((a2:a22="P")*(b2:...

How to ignore #NA in subtotals?
I have several data tables of identical layout that are updated daily, the first two manually and the others by preset formula that calculates from the first two. If a zero or no data are input into cells in the first two then the formula of the others will return #NA, this is intentional because charts are plotted from the latter tables and #NA prevents zero's being plotted all over it for data that has not been entered yet (zero is a valid in negative and positive data entry only when entered). Each table has a SUM total, but, if #NA exits in any one of the cells totaled the S...

Subtotals #2
When the value in column A changes, I need the amount- subtotals for each of the values in columns B and C. (See sample spreadsheet below For example, for "687223007-5", I need: For "19-038": $ 388.89 For "68-1366": 30.36 For "68-1378": 3.51 For "68-1856": 12.72 For "68-2232": 332.91 For "68-4115": 353.20 (Keep in mind that, for the value in column A, the number of entries in columns B and C may range from 1 to ???). Gary A B C ...

Printing what I can see on the screen
On a monthly basis, I need to generate a simple 10 line report from a very large spreadsheet. Using the Subtotals function under the Data menu on the toolbar, I can get the results on screen in about 30 seconds. But how can I either print this out or preferably cut and paste into a Word or email without all of the data. Thanks. Jim ...

Grand Totals without subtotals
Hi, I am preparing a number of tables, all of different sizes. I woul like to record a command in a macro that will calculate the Grand Tota of a specific column even though the number of rows in that column wil vary for each table I prepare. The 'subtotals' command is perfect fo this, except I do not require to subtotal any column at a specifi change, only Grand Total the entire column. I have tried using simple 'sum' forumla, however this doesn't seem to work in tables wit a larger/smaller number of rows as it selects the exact cell I recorde in the macro. Does any...

Refreshing Data loses subtotals
I am currently running a database query from an access db in Excel 2000. I have it set just the way I want, then I decide I'd like a subtotal of something. I create the subtotals the way I'd like and save the spreadsheet. Everything is great until I change the data in the db and click refresh data, at that point I lose my subtotals. Is there a way to save a spreadsheet and its subtotal functions so it will retain them the next time it is refreshed? I was looking at External Data Range Properties and there are functions I can't understand that may hold the key... or not. If so...

Subtotals by page
Is there any way of getting excel to generate subtotals by page? these should update automatically when pagination changes. ...

Pivot Table unable to Include Hidden Items In Subtotals
I have set up a pivot table that lists only the Top 10 values in a column, but also want the subtotal for the entire column, including the hidden items. Excel Help tells me that I can click the "Include Hidden Items in Totals" button, but the button is not available. I followed the help instructions to first select "Subtotal Hidden Page Items" then to unselect it and didn't get my result in either case. Any ideas on why the button is not available? ...

Subtotals #12
I have a spreadsheet and have used the subtotal (Data --> Subtotals). I have chosen to subtotal dollar amount for a particular vendor number, however the names of the vendors does not appear then. How can I get the name of the vendor to appear as well? Thanks, Instead of subtotals, you could use a pivot table to summarize the data. Jon Peltier has instructions and links on his web site: http://www.peltiertech.com/Excel/Pivots/pivotstart.htm Add Vendor number and Vendor name to the row area, and put the dollar field into the data area, where it will become Sum of Dollars. TJAC...

pivot table row subtotals
Excel 2003 I am pretty good at pivot table but can't figure out how the have subtotals display for my 3rd row field in under my 2nd row field - they only display at the grand total line. Please see the example below. I need to get subtotals for A/P and OP under store 1 and then again under store 2. Also My calculated field do now show up in my grand totals? why not - they should work - the math is valid. thanks!!!! Div Desc Desc Ver Store 1 4 OP A/P 1 OP A/P 2 OP A/P 3 OP A/P Store 1 Total Store 2 5 OP A/P 6 OP A/P 7 OP A/P 8 OP A/P Store 2 Total Move ...

Help with Pivot Table subtotals?
My Pivot Table looks like COOLANT COOLANT 50/50 GALLONS COOLANT 50/50 Total COOLANT 60/40 GALLONS COOLANT 60/40 Total COOLANT Total I'd lke to get rid of the "Coolant 50/50" and "Coolant 60/40" subtotal lines. Double-clicking "Coolant 50/50" also makes "Gallons" go away. Is there a setting or property that will do this? Ed Apologies - I tried pasting in an actual table from Word and it didn't take too well! But - I did find the answer: Field Settings, select None under Subtotals. Ed ...

Avereging the Subtotals in Reports
I am trying to do this in the Reports. Lets say, in the details, there are different account numbers with corresponding balances. These balances are totalled in the footer of that Group (month) =Sum([balances]). Then, I need to Average all the monthly totals in the footer of the Report. In the report footer, if i use =Avg([balances]) , it averages ALL the details, NOT the monthly totals. Any help? thanks. I would create a totals query that can be added to the report's record source query. For instance, if you want to find the Average of the Monthly Freight values fr...

Why use Subtotals 1-11 instead of the built-in functions directly?
I see the need to use 101-111 for Subtotals; e.g. I want to ignore hidden values. But, I don't see why I want to use Subtotals 1-11, instead of using the built-in functions like average, count, max etc. directly. I do understand that if I use Data>Subtotals, I can later modify the Subtotals function which may have 1-11. Please enlighten me. Why would I choose Subtotals 1-11 over a more direct method? Am I missing something obvious? Thanks. Epinn Epinn wrote: > I see the need to use 101-111 for Subtotals; e.g. I want to ignore hidden > values. > > But, I don't ...

BOLDING SUBTOTALS
Is there a way to bold subtotals. I want everything on the subtotal line to be bolded, sometimes the report is very big and right now I have to select each row and bold each row. How are your Subtotals created? Data / Subtotals feature or Pivot table or Manually? Are all your subtotals preceded by text such as "Subtotal". -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permi...

list of subtotals!
how can i get a list of subtotals? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26725 View this thread: http://www.excelforum.com/showthread.php?threadid=499188 Hi Via 135, This was found in excel help. Function_Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP You can also look at this link http://www.contextures.com/xlFunctions01.html Larry -- keithl816 ----------------------------------------------...

SmartList Builder SubTotals
Hi, Our client would like to create SubTotals in his SmarLists with SmartList Builder. I don't believe this is available. I seem to recall a 3rd party SmartList solution which incorporated some of this Excel like functionality. thoughts? thx, Doug -- Doug Wilson Great Plains Consultant MCS Canada http://www.dynextra.com/news.asp?news_id=40#replies -- www.DynExtra.com A resource for the Microsoft Dynamics Community Featuring FAQs, File Exchange and more Current member count: 40 -------------------------------------------- Share your knowledge. Add your favorite questions and ans...

Subtotals Formatting
Hi, Is it possible to change the formatting of a set of subtotals such that they for instance could be displayed in red to make them stand out, without having to change each individual one? Cheers Simon Simon One way is to show the detail you wish to change, then use Edit/Go to/Special/Visible cells. This will select all visible cells only, which you can then format as you wish. Andy. "Simon Topping" <anonymous@discussions.microsoft.com> wrote in message news:099501c39fb5$dce555a0$a601280a@phx.gbl... > Hi, > > Is it possible to change the formatting of a set...

Excel subtotals, add a sort option, and BOLD the function answers
love using the subtotal option. sometimes forget to sort the list first, would be nice to have a sort option or reminder. Also when subtotals are applied, the heading or titles for the subtotal is BOLD, the entire line that was added should be bold, or have options to automatically add borders if you'ld like to email microsoft with your comments the email address is mswish@microsoft.com however, if you're asking how can the whole line go bold when using subtotals - conditional formatting is often used to achieve this. Post back if you'ld like more details Cheers JulieD...

Beginner question for months and subtotals
I am new to Excel (have limited exp with Word vba). I would like t enter a list of data where column 1 shows a cost item on the lef followed by the 12 months of the year. Row 1 will be for net, gross tax, date inputs. Every month should have a subtotal. The plan is t make a master sheet which I can use again for the next year. I'm able to do this manually by creating an extra row in th appropriate month and then at the end of the month make my subtotal using the autosum icon, etc. The problem is that I never know how many costs are coming for th month and so for the master sheet I don...

Trouble with subtotals
I have data exported from crystal reports 9 and want to subtotal. I subtotal and it gives me the subtotals but not the groupings. Any ideas? Question answered on a post from 10/22/05. Tools\view\check outline symbols "NYbills" wrote: > I have data exported from crystal reports 9 and want to subtotal. I subtotal > and it gives me the subtotals but not the groupings. Any ideas? ...

Subtotals by Year?
Hi - here's the scenario: Column A is a person's name. Column B is a date, e.g., 4/1/1979. Column C is an amount of money. I'd like to subtotal this spreadsheet by date - but not by individual day, rather by year - i.e. I would like one subtotal for 1979, another for 1980, etc. Is this achievable? Thanks very much. try =sumproduct((a2:a22="joe")*(year(b2:b22)=1979)*c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message news:4f97de2b-fde7-4548-98bc-a17...