Please help... Link table info to a spreadsheet

[mcalabrese79]

I am trying to simplify my "estimate" database. I keep one workbook with basic estimate info (i.e. date, rep name, customer name, address, phone & email) and then I have to switch to another workbook to enter this same info in and then fill out the data for the estimate. I am trying to create something that will link the 2 together and allow me to go into the estimate and click a customer name from a "drop down menu" and it will fill in the rest of the info automatically. Is this doable? If so, any tips on how to create this?
Thank you!!!

 

[etaf]

you may be able using an index/match or vlookup
But first - this is in another workbook and not in the same sheet

Are both workbooks open at the time or closed
also how many customers are there , that maybe in the dropdown list

 

[mcalabrese79]

you may be able using an index/match or vlookup
But first - this is in another workbook and not in the same sheet

Are both workbooks open at the time or closed
also how many customers are there , that maybe in the dropdown list

I add customers to it daily. I can keep both open at the same time if necessary.

 

[etaf]

you can use index/match to get the data from another workbook

however to create a dynamic customer list will need the following

So assuming that the customer database is called customer.xlsx and the estimate book is called estimate
and they are both in the same folder on the same PC

Customer.XLSX - has all the customers in column A

you would need to create a named range in the estimate workbook - that refers to the customer database

in estimate got to name manager
create a name say
MyCustList
and in the souce
put
=OFFSET([customers.xlsx]Sheet1!$A$1,1,0,COUNTA([customers.xlsx]Sheet1!$A:$A)-1,1)

that will create a dynamic range from your customers

now goto the sheet you want the dropdown and create a datavalidation list
in the source you need to use F3
to get the list of named ranges
and select
MyCustList

Now in the adjacent cells you can use index match
=INDEX([customers.xlsx]Sheet1!$B:$B,MATCH(A2,[customers.xlsx]Sheet1!$A:$A,0))

i have attached a share with a couple of spreadsheets to show how this all works

https://www.dropbox.com/s/4q9txntsnt8beq5/estimate.xlsx?dl=0
and
https://www.dropbox.com/s/993bwjfu62gtld8/customers.xlsx?dl=0

save both files into the same folder
it should work
BUT
it may have picked up the full path

c:\users\username\documents\ etc

so you may need to edit that out
you do not need the path - if the spreadsheets are in the same folder

if you have a lot of customers the dropdown list - is going to be huge

 

[mcalabrese79]

awesome!! I am going to try this out right now. thank you!