Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
offset | Excel Worksheet Functions | |||
OFFSET HELP | Excel Worksheet Functions | |||
VBA help with Offset | Excel Discussion (Misc queries) | |||
Max Offset | Excel Discussion (Misc queries) |