Subtotal function for hidden row

[tpolak]

I have got a multisheets database where both rows and columns may be
hidden according to a given criteria. Only blanks rows and columns
should be hidden.

Now I need a formula to check if there is no "valuable" data hidden.

Checking test might be:
SUBTOTAL(9,reference) = SUBTOTAL(109,reference)

The problem is that it works for rows only and not for the columns.
Any ideas?

Thanks in advance
Tomek

 

[Roger Govier]

Hi

One way to deal with your columns would be
=COUNT(A1:X1)<>COLUMN(X:X)+1-COLUMN(A:A)
This would return TRUE if there are no values in the hidden columns.

Regards

Roger Govier

 

[tpolak]

It seems that Count doesn't take into account whether a cell is hidden
or not. It counts it anyway...

 

[Richard Buttrey]

I have got a multisheets database where both rows and columns may be
hidden according to a given criteria. Only blanks rows and columns
should be hidden.

Now I need a formula to check if there is no "valuable" data hidden.

Checking test might be:
SUBTOTAL(9,reference) = SUBTOTAL(109,reference)

The problem is that it works for rows only and not for the columns.
Any ideas?

Thanks in advance
Tomek

You could try playing around with the cell width value of columns
since a hidden column will return zero.

For example:
E1:G3 contains your data
E F G
1 1 10 20
2 2 11 30
3 3 12 40

A1:A3 contains the column letters of your data, i.e. A1=E, A2=F, A3=G

Now put the following in B1 and copy down to B3

=IF(CELL("width",INDIRECT(A1&"1"))=0,"Hidden","Visible")

Now put the following in C1 and copy down to C3

=SUM(INDIRECT(A1&"1"):INDIRECT(A1&"3"))

Column B will tell you whether your data columns are hidden or not,
and column C will tell you the aggregate value of the columns. You
could modify C1 etc formulae to only show a total if column B1 etc is
"Hidden".

Hide say column F with Format Column Width to zero, and press F9 to
recalculate to test. For some reason you have to do this since the
formulae don't appear to re-act when you first set widths to zero.

The above won't alert you to a hidden column which contains positive
and negative data that aggregates to zero. You might want to modify
things with array formua formulae to test for that condition.

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Roger Govier]

Hi

Agreed, but that is useful.
If the value returned by Count is the same as the value returned by
counting the used column range, then there must be data in every cell,
then the returned value would be FALSE
If some of those cells (columns) are Hidden, this means your hidden
columns did contain data.

Regards

Roger Govier

 

[tpolak]

Thanks. This method is well useful but I find it little convenient. I
mean for each of 50 columns I should have 50 rows with "Visible
status". This strange geometry does not look good to me. I was rather
thinking of a "magic formula" in one cell with the clear response
"GOOD" or "WRONG".

This is my exercise:
A1, B1, C1 = 7 {Values}
A1:C1 = "DATA"

E1=SUM(IF(CELL("WIDTH",A1)>0,1,0)) {Visible Status}
F1=SUM(IF(CELL("WIDTH",B1)>0,1,0))
G1=SUM(IF(CELL("WIDTH",C1)>0,1,0))

Hiding A, B, C results in E, F, G. When B is hidden E=1, F=0, G=1.
The Visible Status is known. Now the cell values should only be added
based on the Status.

I1 = {SUM(IF(CELL("WIDTH",data)>0,data,0))}

Unluckily it does not work that way. Where am I wrong?
Tomek

 

[tpolak]

={SUM(IF($E$1:$G$1>0,data,0))} gives the right response. The problem
is that for the each test (A:C) I should have a mirror array (E:G) what
is very inconvenient with big multiworksheets database.
TP, www.vba.blog.onet.pl

 

[tpolak]

I finally implemented the solution of Visible Status in Cells not in
Memory. It works fine!

 

[Richard Buttrey]

I finally implemented the solution of Visible Status in Cells not in
Memory. It works fine!

I've just been experimenting with a refinement to it. I'd be
interested to know what you've now got.

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[topola]

My pleasure indeed. See the following:

V_RNG = A1:X100 '$Value Range

S_RNG = A200:X200 'Visible $Status
A200 = CELL("width",A$1)

AA1:AA100 'Simple Sum
AA1 = SUM(A1:X1)

AB1:AB100 'Visible Sum
AB1={SUM(IF(S_RNG>0,A1:X1,0))}

AC1:AC100 'Sum Checking = Simple Sum Minus Visible Sum
AC1=IF(ROUND(AA1-AB1,2)=0,"ok","BAD")

AD1:AD100 Point Hidden Columns - Sums the number of Wrong Hidden
Columns
AD1={SUM(IF(S_RNG=0,IF($A1:$X1<>0,COLUMN($A1:$X1),0),0))}

AD1:AD100 Point Hidden Columns - Counts the Wrong Hidden Columns
AD1={SUM(IF(S_RNG=0,IF($A1:$X1<>0,1,0),0))}

It makes me feel much more comfortable now.
__
Tomek Polak
Enterprise Investors

 

[topola]

While searching Wrong Hidden Columns it is useful to toggle between
ReferenceStyles - I use small buttons with those macros.

Sub Style_A1()
Application.ReferenceStyle = xlA1
End Sub

Sub Style_R1C1()
Application.ReferenceStyle = xlR1C1
End Sub