Excel Formula

Discussion in 'Business Applications' started by JudyB, Nov 7, 2003.

Not open for further replies.
1. I'd like to create a formula (some kind of IF statement) to test a range of cells to determine if any hidden rows contain values. The statement could return a statement like "Alert - hidden values" if it returns true.

We have a billing sheet, and some of the users tend to hide rows that don't apply, forgetting to clear the values first. Then, the total does not appear to calculate correctly.

Any ideas? I know how to do the if statement, I just don't know how to test the range of cells in a formula. Thanks.

2. Assuming they would be positive numbers (and in D1 thru D10, then

=IF(COUNTIF(D1 10,"> 0") > 0, "Alert - Hidden Values"," ")

3. They are positive numbers, and I had thought of that. But, since there are numbers in the other rows, with hidden rows scattered in the range, that formula would pick up the "good" numbers as well.

Anyway to "test" if the cell his hidden?

4. Can't get my head round this formula-wise for now. However, selecting the range will flag up any disparity via AutoCalc (eg: Sum= in the Status Bar).

If I think of anything else I'll let you know.

Rgds,
Andy

5. Thanks. I know that they won't always do that to check their work. But, if I don't come up with anything else, at least thats a good way to go in and occasionally "audit" their forms.

6. Voila! Found this UDF, apparently a former MSKB article. Seems to work for numeric values (whereas SUBTOTAL only works for rows hidden by filtering, of course), just work in your alert message.

Rgds,
Andy

Function SumVisibleCells(CellsToSum As Range)
With Application
.Volatile
.Calculation = xlManual
End With
For Each cell In CellsToSum
If cell.Rows.Hidden = False Then
If Columns.Hidden = False Then
Total = Total + cell.Value
End If
End If
Next
SumVisibleCells = Total
With Application
.Calculation = xlManual
End With
End Function

7. Thanks Guru. I think this is progress. I've entered a formula based on that function, and it works. I just have to figure out how to get it to automatically recalculate if additional rows are hidden or unhidden. I think there must be a way to make the sheet or that cell automatically recalculate either when a change is made, or when you click anywhere on the sheet, or ???

I think I can work on this and figure it out, but if you have any quick tip, I'd be grateful as well.

Judy

8. What are the chances that the last thing a user does to the sheet is hide row(s) (ie: without then clicking on any of the cells?) ; if slim to none, then, in the worksheet module -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A12") <> Range("C1") Then
Application.Calculate
End If
End Sub

HTH,
Andy

9. Thanks for your help Andy. I finally got a chance to finish this. I had to do it a little differently that you said above, because your routine would work only when I would "unhide" a row. When I would hide the row, it would apparently still see the totals in the two cells as being equal and would not recalc.

So, I expanded the code as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Rows(5).Hidden Then
Application.Calculate
End If
If Rows(6).Hidden Then
Application.Calculate
End If

I went through 25 rows individually because I couldn't
figure out how to test a range of rows. Then I added
the statement you gave me:

If Range("G25") <> Range("I25") Then
Application.Calculate
End If
End Sub

I might have been able to make it shorter, but it works great. Thanks for your help!
Judy

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!