Extract list from another wb

[Prasad]

I have a wb (say book 1) with data in tabular form with Item Name in Col A & Qty for each item in Col B. One item may appear more than once. In another WB (book 2) in Col A, I want to make a drop-down with list from Col A of book 1 & in col. B, pull the list of qty based on such item.

I have made a drop-down in Col A of book 2 with named range of Col A of book 1 but not able to extract the list of qty in col B.

Looking for a VBA/formula solutions.

PS: Pl note that these are two different workbooks in different locations.

[HansV]

If you created a validation dropdown, you only get the value selected by the user, not its position in the list, so if a name occurs more than once, you have no way of determining which of the multiple occurrences is selected, and hence no way to decide which quantity you need.
The same goes if you use a combo box from the Control Toolbox.

But if you use a combo box from the Forms toolbar, the linked cell of the combo box will contain the index of the selected item - 1 for the first item, 2 for the second item, etc. You can use this to create an INDEX formula to return the value from column B in Book 1 corresponding to the name selected in the combo box.

[Prasad]

Thanks Hans but I have mistranslated the requirement. In case of multiple occurrence of items (It would be an added advantage if item appear only once in drop-down), I need to extract the LIST of qty.

Pl have a look at attached workbook. If I select item "100" from the drop-down, it should list down all 3 qtys. in column B one below other.

Hope I made this clear.

[HansV]

Does the attached version help? The code for the Worksheet_Change event of Sheet2 uses Advanced Filter to copy data from column B on Sheet1 for the selected item.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A2"), Target) Is Nothing Then
    Worksheets("Sheet1").Range("A1:B8").AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=Range("A1:A2"), _
      CopyToRange:=Range("B1"), _
      Unique:=False
  End If
End Sub

Book1.xls

[Prasad]

It is working fine if both sheets are in same workbook, but the problem is that they are two different workbooks, saved in different folders & the code is required to amend a little bit.

Is it possible to avoid multiple occurrence of items in drop-down menu?

[HansV]

You could use Advanced Filter with the 'Copy to another location' and 'Unique records only' options to create a list of unique items from column A, and use this list for the dropdown on the other sheet.

[Prasad]

Hans, apology but I have not explained the problem properly.

Book 1
I have a workbook, which is being updated by other users (one at a time) all the time. User feed the item name in col A & qty against that item in col B. There are several columns with data which is irrelevant to me.
Book 2
Now I want to create automated summary of Book 1, with drop-down in column A from Column A of Book 1 (item Name to be appeared only once) & pull entire list of qty in column B from column B of Book 1.

These are two different workbooks in different folders but on same machine.

[HansV]

This is a database-like application; I'd use Microsoft Access or another database application for it instead of Excel.