Offset???

K

Ken

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
 
H

HelpExcel.com

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
 
K

Ken

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Numbers from first column 6
Index/Match (maybe?) 9
Reverse Unique Values 1
Rank and return Names 5
Return every 50th Value? 6
Min? 3
Conditional formatting 3
Lookup value and return column offset value 6

Top