Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom views and formulas
Hoping someone can tell me if my understanding of custom views is correct.
I have been given a spreadsheet of around 100 rows. The rows all contain a dollar amount in column "N". Ther person who set up this sheet has many custom views set up, which look to be hiding various rows and columns. My concern is this; there is a total at the bottom of column N, which is a basic sum, giving the total of all the dollar amounts in column "N". The people in the office seem to think that by setting up the different views, and hiding some rows, the figures in the hidden rows will not be included in the sum total and the sum will change to leave out these cells. After a quick test, this does not seem to be the case at all. Would a better way for them to do this be to actually set up different sheets, either leaving in or leaving out the rows containing the figures as desired? I hope this makes sense, I don't really have the confidence to make a call on this, and would appreciate any ideas. Thanks. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom views and formulas
Hi Andrea,
My concern is this; there is a total at the bottom of column N, which is a basic sum, giving the total of all the dollar amounts in column "N". The people in the office seem to think that by setting up the different views, and hiding some rows, the figures in the hidden rows will not be included in the sum total and the sum will change to leave out these cells. After a quick test, this does not seem to be the case at all. You could use the SUBTOTAL function. By default, the SUBTOTAL worksheet function leaves out filtered rows, but includes hidden rows. As of Excel 2003, when used with the new argument starting from 100, it also leaves out hidden rows. So: SUBTOTAL(9,A1:A1000) only sums visible rows which have been filtered using autofilter, but also sums rows hidden otherwise SUBTOTAL(109,A1:A1000) only sums visible rows, whichever way they have been hidden. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custom views and formulas
Thankyou so much Jan, I think this is exactly what we need.
"Jan Karel Pieterse" wrote: Hi Andrea, My concern is this; there is a total at the bottom of column N, which is a basic sum, giving the total of all the dollar amounts in column "N". The people in the office seem to think that by setting up the different views, and hiding some rows, the figures in the hidden rows will not be included in the sum total and the sum will change to leave out these cells. After a quick test, this does not seem to be the case at all. You could use the SUBTOTAL function. By default, the SUBTOTAL worksheet function leaves out filtered rows, but includes hidden rows. As of Excel 2003, when used with the new argument starting from 100, it also leaves out hidden rows. So: SUBTOTAL(9,A1:A1000) only sums visible rows which have been filtered using autofilter, but also sums rows hidden otherwise SUBTOTAL(109,A1:A1000) only sums visible rows, whichever way they have been hidden. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Views | Excel Discussion (Misc queries) | |||
Custom Views | Setting up and Configuration of Excel | |||
Custom Views | Excel Discussion (Misc queries) | |||
Custom views | Excel Discussion (Misc queries) | |||
Custom Views | Excel Discussion (Misc queries) |