vlookup more then 1 match or how do I

[magicaldean]

Hello and thanks for taking the time to look and try to help

I have a list of info on sheet "open orders" with columns A=SO#, B=ID#,
C=QTY, that uses a choose function to get this info from a different
sheet. there might be a SO# listed from 1-30 times with a different ID#
and QTY in the next columns

On sheet "items" I want to type a SO# (listed on the "open orders"
sheet) into cell "B1" and have a list of all ID# and there QTYs be
listed on sheet "items" in cells A4 and B4 and down.

please look at this file i have attached as a idea of what i am trying
to say

please let me know any questions you may have


+-------------------------------------------------------------------+
|Filename: help.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=171|
+-------------------------------------------------------------------+

 

[Roger Govier]

Hi

Insert a new row on your Goal Sheet.
In cell B1 type SO No
In Cell B2 enter the Order Number you want.

In cells A4 and B4 type Item ID and Qty Rem
Note - these must be the same as the column headings on your Open Orders
Sheet.

Copy the following event code to the Goal Sheet

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$B$2" Then Exit Sub

Sheets("open orders").Range("A:C").AdvancedFilter Action:=xlFilterCopy,
_
CriteriaRange:=Range("B1:B2"), _
CopyToRange:=Range("A4:B4"), _
Unique:=False

End Sub

Copy the Code above
Right click Goal Sheet tab > View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

This is event code which will be triggered automatically as you enter a new
number in cell B2