# Thread: Find a cell meeting these conditions (Excel 2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•