Using dropdown list to pull data from another workbook

3kidsgrayhair

New Member
Joined
Jan 26, 2005
Messages
3
Hello, I am a new member getting my feet wet with Macros. I have used the board to find some help but am having trouble with this problem.

1) Problem. I have two workbooks, one with customer info ("Customers") and another we are using to create and track projects ("T&M Project Sheet2"). In the T&M workbook I have included a drop down list from the forms toolbar listbox that lists Customers from the Customers workbook. When the user selects the Customer in the dropdown, I would like to draw more information about the customer (address, phone, etc) to populate specific cells on the T&M workbook.

2) Efforts. I can get the list to output the row number of the selected customer to a cell. I used the Macro recorder to copy and paste data, but it uses the specific row number I recorded, not the variable based on the customer selection. An abbreviated version of the code is below:

Sub customerfill()
'
' customerfill Macro
' copy and paste customer info
'

'
Application.ScreenUpdating = False
Windows("customers.xls").Activate
ActiveWindow.SmallScroll Down:=48
Windows("T&M Project Sheet2.xls").Activate
Windows("customers.xls").Activate
Range("D100").Select
Selection.Copy
Windows("T&M Project Sheet2.xls").Activate
Range("O13").Select
ActiveSheet.Paste
Windows("customers.xls").Activate
Range("B100").Select
Application.CutCopyMode = False
Selection.Copy
Windows("T&M Project Sheet2.xls").Activate
Range("O14").Select
ActiveSheet.Paste
End Sub

3) Questions. Can I replace the recorded row (100) with a variable based on the drop down list (i.e. 245)? If so, how do I do that? Is there a better way to do this?

Thanks so much for your help.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
what I would do is use a vlookup fomula for this instead of the macro... lets say your drop down list is in a1 on your t&m sheet and your list comes from a range on your customers sheet in the "A" column. To the right of your names in customers sheet you have columns with their info.... column b is phone number, column c is address...... now go like this

on your t&m sheet in b1 typ a fomula similar to this...

=IF(ISNA(VLOOKUP($A$1,'C:\Documents and Settings\Administrator\Desktop\[customers.xls]sheet1'!$A$1:$C$100,2,FALSE)),"",VLOOKUP($A$1,'C:\Documents and Settings\Administrator\Desktop\[customers.xls]sheet1'!$A$1:$C$100,2,FALSE))

(the path of the file will be different but I hope you get the idea)

now in c1 on your t&m sheet put this formula.... notice i changed the 2 to a 3 in both sections of formula

=IF(ISNA(VLOOKUP($A$1,'C:\Documents and Settings\Administrator\Desktop\[customers.xls]sheet1'!$A$1:$C$100,3,FALSE)),"",VLOOKUP($A$1,'C:\Documents and Settings\Administrator\Desktop\[customers.xls]sheet1'!$A$1:$C$100,3,FALSE))

now if you select one of the names in your drop down the other 2 cells next to it fill in automatically


how it works:

$a$1 is the cell that the formula is looking for a data change in (dropdown box)

$A$1:$C$100 is the range on your customer sheet that the formula looks in once you select a name in the dropdown


2 or 3 is the column number to copy the info from.

Hope this was helpful
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Something like this could go in your ComboBox1_Change() module:

Did you say you used a listbox instead? The code would be similar. I used a ComboBox because I figured you would only want one selected at a time. Post back if that's a problem.


Code:
Private Sub ComboBox1_Change()
Dim cell As Range

    For Each cell In Sheets("Customers").Range("CustomerList")
        If cell.Value = ComboBox1.Value Then
            Sheets("T&M Project Sheet2").Range("B2").Value = cell.Offset(0, 1)
            Sheets("T&M Project Sheet2").Range("C2").Value = cell.Offset(0, 2)
        End If
    Next cell
    
End Sub

It's not the nicest code in the world, but it gets the job done. You may have to adjust your ranges some. I created a range "CustomerList" on the sheet "Customers" that consists of all the customer names, but you could use cell references instead if you prefer. Hope this helps!

Here's what my sheets look like, by the way:
Customers sheet
Book2
ABCD
1CustomerNameAddressPhone
2CustomerAAddressAPhoneA
3CustomerBAddressBPhoneB
4CustomerCAddressCPhoneC
5CustomerDAddressDPhoneD
6CustomerEAddressEPhoneE
7CustomerFAddressFPhoneF
Customers


T&M Project Sheet2 (The combo box over cell A2 won't be visible:
Book2
ABCD
1CustomerNameAddressPhone
2AddressFPhoneF
T&M Project Sheet2
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Taz,
The "grey haired one" (and believe me, I'm not one to speak! :LOL: ) is pulling the customer info from a different workbook altogether.

I would go with d0wnt0wn's idea, but instead of using the listbox from the Forms toolbar, you might look at using an ActiveX listbox (from the Controls Toolbox toolbar). This will show you the actual text of your selection instead of just the index number of it in your list.

Dan
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
I see that now. Still, a Workbook object could be added to the Sheets property in the code I posted. I agree that d0wnt0wn's solution is probably simpler to implement, but who am I to argue with the OP? (y)
 

3kidsgrayhair

New Member
Joined
Jan 26, 2005
Messages
3
Thanks jfor all the valuable input. I will try these suggestions tomorrow when I get back to my task.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,695
Messages
5,626,357
Members
416,174
Latest member
LavendarRabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top