Trying to reference a date using a specific criteria.

[jjwapp]

Looking for a formula that allows me to find the date for the first value given >0.
For example, P/N 70032 want to find 7/29/2019 date.



[TABLE="width: 304"]
<tbody>[TR]
[TD="width: 53, bgcolor: transparent"]P/N
[/TD]
[TD="width: 74, bgcolor: transparent"] Past Due
[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]7/8/2019
[/TD]
[TD="width: 69, bgcolor: transparent, align: right"] 7/15/2019
[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]7/22/2019
[/TD]
[TD="width: 69, bgcolor: transparent, align: right"] 7/29/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]70025
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]70028
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]70032
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]172
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]70042
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sijpie]

I can't think of a combination of formulas to do this other than an inflexible list of nested IF() functions combined with OFFSET()

I have written a small UDF (user defined function) that will do it for you. It also has a restriction (as a UDF can't use the 'CurrentRegion' property of a range): The heading of the partnumber column has to be "P/N". If not then you will have to modify that in the macro.

Press the Alt-F8 key in Excel and choose 'New'. This will open the VBA editor. In the editor window (right empty pane) paste the function below.
Save your file as a macro enabled workbook.

Now you can use the function by typing into a cell: =getdate4pos( then click with the mouse on the partnumber and close the bracket. You will notice that ecxel shows the function as you type.

Function GetDate4Pos(rCell As Range) As Date
'///////////////////////////////////////////
'// Function to return date in top row of //
'// range with partnumbers in left column.//
'// Date returned is for first non-zero   //
'// cell to right of partnumber.          //
'// Top left cell of range should have    //
'// text "P/N"                            //
'// ------------------------------------- //
'// Usage: =GetDate4Pos(A5) where A5 will //
'// be a cell with partnumber in the left //
'// column.                               //
'///////////////////////////////////////////

    Dim vAr As Variant
    Dim i As Long, c As Long, j As Long, UB1 As Long, UB2 As Long
    Dim sPN As String
    
    Application.Volatile True
    sPN = rCell.Text
    For i = 1 To rCell.Row - 1
        If rCell.Offset(-i, 0) = "P/N" Then
            UB1 = rCell.Row - i
            Exit For
        End If
    Next i
    c = 1
    Do While Len(rCell.Offset(-i, c))
        c = c + 1
    Loop
    UB2 = c - 1
    
    For c = 1 To UB2 + 1
        If rCell.Offset(0, c) > 0 Then
            Exit For
        End If
    Next c
     GetDate4Pos = rCell.Offset(-i, c)
    
End Function