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.
Extract list from another wb
-
- StarLounger
- Posts: 62
- Joined: 27 Jan 2010, 05:00
Extract list from another wb
Regards
Prasad
Encourage Environmental Awareness| Print only if unavoidable
Prasad
Encourage Environmental Awareness| Print only if unavoidable
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract list from another wb
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.
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 62
- Joined: 27 Jan 2010, 05:00
Re: Extract list from another wb
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.
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.
You do not have the required permissions to view the files attached to this post.
Regards
Prasad
Encourage Environmental Awareness| Print only if unavoidable
Prasad
Encourage Environmental Awareness| Print only if unavoidable
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract list from another wb
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 62
- Joined: 27 Jan 2010, 05:00
Re: Extract list from another wb
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?
Is it possible to avoid multiple occurrence of items in drop-down menu?
Regards
Prasad
Encourage Environmental Awareness| Print only if unavoidable
Prasad
Encourage Environmental Awareness| Print only if unavoidable
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract list from another wb
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 62
- Joined: 27 Jan 2010, 05:00
Re: Extract list from another wb
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.
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.
Regards
Prasad
Encourage Environmental Awareness| Print only if unavoidable
Prasad
Encourage Environmental Awareness| Print only if unavoidable
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract list from another wb
This is a database-like application; I'd use Microsoft Access or another database application for it instead of Excel.
Best wishes,
Hans
Hans