Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 112
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 535
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 112
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Views Khardy3352 Excel Discussion (Misc queries) 0 June 30th 08 12:28 AM
Custom Views KeithMeister Setting up and Configuration of Excel 2 October 30th 07 10:58 PM
Custom Views Dave F Excel Discussion (Misc queries) 1 November 17th 06 05:11 PM
Custom views Wagi123 Excel Discussion (Misc queries) 2 February 10th 06 11:03 AM
Custom Views GLT Excel Discussion (Misc queries) 0 October 21st 05 06:15 PM


All times are GMT +1. The time now is 08:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"