I have an excel spreadsheet with column headings and row headings. The column headings are dates while the row headings are drawing numbers. Each time a drawing was revised an x would go under the date it was done. There are multiple x's per row. I was looking for a formula that would produce the most recent date of each drawing. If that is not possible a combination of the dates would also be acceptable.
You will need to use a user defined function in VBA to determine the last used column location with an X value. Once you have the column, you can apply this to the row number with the dates to get the last date either in VBA or via a formula INDIRECT.
The user defined function will take the row range such as:
VBA Code:
Function LASTCOL(rngRow As Range) As Variant Dim tmpRange As Range Dim i As Integer, cnt As Integer Application.Volatile Set tmpRange = rngRow.Rows(1).EntireRow Set tmpRange = Intersect(tmpRange.Parent.UsedRange, tmpRange) cnt = tmpRange.Count For i = cnt To 1 Step -1 If Not IsEmpty(tmpRange(i)) Then LASTCOL = tmpRange(i).Value Exit Function End If Next i End Function
You can change the user defined function slightly by returning the column number instead of the value. To do so replace LASTCOL = tmpRange(i).Value with LASTCOL = tmpRange(i).Column
