How to Use a Worksheet Last Row Function in a sheet cell formula

[rfs0123]

I have around a hundred cells that do a sumproduct formula to calculate total footage of various sized pipe listed on a worksheet called 'Detail' (see the formula below. This works in around 90% of the situations because the total rows in the 'Detail' sheet is less than 6000, the fixed value in the formular. Rrecently we noticed that on larger jobs and/or different sort combinations we exceeded the 6000 fixed last row. I setup a function to dynamically calculate the last row of the 'Detail' sheet for each job but can't figure out how to replace the 6000 in the formula with the DetailLR() function. I've tried adding "" & and '' &, even placing the function in a cell and using that reference. I've tried using Value(Function), but everything has failed so far. I know that I can write the formula using VBA each time we load the worksheet, but I though there must be an easier way. Any ideas?

=SUMPRODUCT((Detail!$E$10:$E$6000=Utilities!$H$9)*(Detail!$C$10:$C$6000=Utilities!J8)*(Detail!$B$10:$B$6000))+SUMPRODUCT((Detail!$E$10:$E$6000=Utilities!$H$9)*(Detail!$C$10:$C$6000=Utilities!J7)*(Detail!$B$10:$B$6000))

Public Function DetailLR() As Long
Application.Volatile (True)
Dim sh As Worksheet
Set sh = ActiveWorkbook.Worksheets("Detail")
    On Error Resume Next
    DetailLR = sh.Cells.Find(what:="*", _
                            After:=sh.Range("A1"), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Thank you


Bob

 

[James006]

Hi,

You can use the following by Harlan Grove ...

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
    Dim q As Range
    Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
    Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function

HTH

 

[northwolves]

Try:

=SUMPRODUCT((offset(Detail!$E$10,0,0,counta(Detail!e:e)-9,1)=Utilities!$H$9)*(offset(Detail!$c$10,0,0,counta(Detail!e:e)-9,1)=Utilities!J8)*(offset(Detail!$b$10,0,0,counta(Detail!e:e)-9,1)))+SUMPRODUCT((offset(Detail!$E$10,0,0,counta(Detail!e:e)-9,1)=Utilities!$H$9)*(offset(Detail!$c$10,0,0,counta(Detail!e:e)-9,1)=Utilities!J7)*(Detail!$B$10:$B$6000))

 

[rfs0123]

Thank you Harlan. How would I use this in the Cell Formula reference to the Row 6000?

Bob

 

[rfs0123]

Thank you,

Just inserting this formula into my Worksheet does not produce an error, but does result in a #NA value. I may need to tweak or check it a bit more to see whether this will ultimately work. Thank you for for time.

Bob

 

[James006]

Hello again,

With the used function, you could test :

=SUMPRODUCT(MAX((LEN(USED(A:A))>0)*ROW(USED(A:A))))

HTH