Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find a cell meeting these conditions (Excel 2000)

    All,
    Is there a formula to find the last non-zero cell in a row where the cell values are calculated with formulas and display the results in another cell?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a cell meeting these conditions (Excel 2000)

    For example, to return the last non-blank number in row 16:

    =INDEX(16:16,MATCH(9.99999999999999E+307,16:16))

    If you want to exclude zero values, I'll have to think about it.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find a cell meeting these conditions (Excel 2000)

    Hans,
    I found that example on the web and the problem is a zero value.
    The row has 12 columns(months) plus a description in the first column.
    As they fill in the template sheet every month, the next column of the row(data i'm looking for) will data in it.
    I'm using this function to calculate the values in the cells
    <pre>Function SumAll(sCell As String)
    Dim wks As Worksheet
    Dim dSum As Double
    Dim iCount As Intege

    Application.Volatile
    iCount = 0
    dSum = 0
    For Each wks In Worksheets
    dSum = dSum + Application.Sum(wks.Range(sCell))
    Next
    SumAll = dSum
    Set wks = Nothing
    End Function</pre>


    Should this be modified to make your formula work?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a cell meeting these conditions (Excel 2000)

    The SumAll function is intended to sum a cell (or range) across all worksheets in a workbook. I don't see what that has to do with your original question.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find a cell meeting these conditions (Excel 2000)

    Hans,
    I included the Function to show you how the cells in the row get their value.
    I modified the Function so if there isn't any sum involved, it won't place a zero in the cell.
    <pre>Function SumAll(sCell As String)
    Dim wks As Worksheet
    Dim dSum As Double
    Dim iCount As Integer

    Application.Volatile
    iCount = 0
    dSum = 0
    For Each wks In Worksheets
    dSum = dSum + Application.Sum(wks.Range(sCell))
    Next
    If dSum = 0 Then
    SumAll = ""
    Else
    SumAll = dSum
    End If
    Set wks = Nothing
    End Function</pre>


    Then using your original formula, I can achieve the results I want.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find a cell meeting these conditions (Excel 2000)

    Oh, OK. Glad you were able to solve it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •