1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula

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

Thread Status:
Not open for further replies.
Advertisement
  1. JudyB

    JudyB Thread Starter

    Joined:
    May 30, 2003
    Messages:
    98
    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. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Assuming they would be positive numbers (and in D1 thru D10, then

    =IF(COUNTIF(D1:D10,"> 0") > 0, "Alert - Hidden Values"," ")
     
  3. JudyB

    JudyB Thread Starter

    Joined:
    May 30, 2003
    Messages:
    98
    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. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    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. JudyB

    JudyB Thread Starter

    Joined:
    May 30, 2003
    Messages:
    98
    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. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    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. JudyB

    JudyB Thread Starter

    Joined:
    May 30, 2003
    Messages:
    98
    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. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    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. JudyB

    JudyB Thread Starter

    Joined:
    May 30, 2003
    Messages:
    98
    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
     
  10. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/177693

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice