Offset???
Excel2003 ...
Col E ... Contains: Empty Cells, "A" & "I" Col W ... Contains: Empty Cells & "Qty's" I wish to return the "Qty" from Col "W" from the 1st record that contains an Empty Cell above the "I" in Col E. Issue is: I can have several records in a row with the "I" indicator & I still wish to return the "Qty" from Col "W" from the 1st record above the "I's" that contain an Empth Cell. ie: Col E Col W Ind Qty -- 10 -- 3 A -- -- 10 -- 7 -- 10* I 3 ... Return 10* -- 20 -- 20 -- 20* I 10 ... Return 20* I 10 ... Return 20* I 5 ... Return 20* I 5 ... Return 20* -- 10 -- 10 Thanks ... Kha |
Function
Ken,
Following is a UDF that should work. Insert a blank module in your workbook and copy in the following code: Public Function aboveI(rngI As Range, rngNum As Range) As Variant If rngI.Value < "i" Then aboveI = "" Exit Function End If Do Until rngI.Value < "i" Set rngI = rngI.Offset(-1) Loop aboveI = rngI.Parent.Cells(rngI.Row, rngNum.Column).Value End Function Regards, Eddie HelpExcel.com |
Function
Eddie ... (Hi)
Code copied into new Module ... However, I do not know how to write against the new Function (abovei) ... =abovei(?????) ... "I" codes are in Col E & I need to return "Qty" from Col "W". Thanks ... Kha "HelpExcel.com" wrote: Ken, Following is a UDF that should work. Insert a blank module in your workbook and copy in the following code: Public Function aboveI(rngI As Range, rngNum As Range) As Variant If rngI.Value < "i" Then aboveI = "" Exit Function End If Do Until rngI.Value < "i" Set rngI = rngI.Offset(-1) Loop aboveI = rngI.Parent.Cells(rngI.Row, rngNum.Column).Value End Function Regards, Eddie HelpExcel.com |
All times are GMT +1. The time now is 07:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com