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.

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

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

.