Finding Values In Separate Workbooks

    I am in need of a formula to put in sales.xls to take cell A2 from sheet1, look for it in sales2.xls, sheet1, and then return that value in sales.xls sheet2. Exactly what I am doing is taking a customer name that an engineer types in a quote sheet and looking it up in another workbook and entering that customer's name and address onto a packing slip. I wanted to be able to do this without having the both sheets in the same file (for fear the guys would delete something). Is this at all possible? I can clarify if needed. Thank you in advance for any suggestions.


    Tiffanny

    Re: Formula For Finding Values In Separate Workbooks


    Hi


    Try VLOOKUP, the wizard will guide you through the various entries.


    HTH


    Robert

    Re: Formula For Finding Values In Separate Workbooks


    Hi Tiffany,


    Sounds simple enough (and it is for a human) but computers need to know so much more!


    If I understand correctly you want to:


    -Put value in Workbook Sales.xls, Sheet1, Range(A2)


    -Find/Look up that value in Workbook Sales2.xls, Sheet1, Range(??)


    -Return name AND address in Workbook Sales.xls, Sheet2, Cells (??)


    Questions:


    Is file Sales2.xls open? Or should we open/close it for lookup only?


    Is the inputted value the name? or the address? or both?


    What range would we expect to find the inputted value in?


    Are the name and address in separate columns?


    Where do they need to return to?


    A small example of what/where might answer some or all of these!


    ie: "Computers always do what you tell them to and NOT what you want them to."


    Thanks!

    Re: Formula For Finding Values In Separate Workbooks


    Thank you for the responses. I am hoping to do this with the sales2.xls workbook closed. So, I guess it would have to open and close for lookup. The inputted value is the name of the customer, but also it is usually an abbreviated name. For instance, instead of Honda Manufacturing, it might just say Honda. The inputted value will be located in the first column of the sales2.xls workbook. Yes, the name and address are in separate columns, although they do read accross a row together.

    Re: Formula For Finding Values In Separate Workbooks


    I have attached a copy of what I want. I would like for it to be able to look at the value in "Customer" box on the EC Quote sheet and look it up in a separate workbook and return the name and address on the Shipper sheet in the "Bill To" boxes.

    Re: Finding Values In Separate Workbooks


    Hi Tiffany,


    First thing is: You might consider having the list in the same workbook on a sheet that is set as xlVeryHidden, and some lookups for the values...


    Here's a starting point based on your example. Two things tho:


    1) Merged cells for the value to look for (Customer name) are not acceptable - too many problems associated with merged cells and code. I changed the column widths and deleted the "merge". If you want to display them wider select two or more columns and format the cells as "Center across selection". They will appear as merged without causing problems for the code.


    2) I set the "Find" code for xlPart as you say that the name inputted may not be the whole name. ie inputted "Honda" may be Honda Motor Manufacturing. So I set the code to check against the LEFT for a match.


    If the list is:


    Honda
    Honda Manufacturing
    Honda Motors Ltd
    Honda Motorcycles


    Honda returns item 1
    Honda M returns item 2
    Honda Mo returns Item 3
    Honda Motor returns item 3
    Honda Motorc returns item 4



    And finally, the filename is hardcoded. You must change this for the code to work for you. Open the VBE and in Module 1 change this line:


    "C:\Documents and Settings\dr\My Documents\street.xls"


    to whatever your drive/directory/filename will be. (Keep it in quotes)



    [vba]
    'CHANGE HARD CODED FILENAME
    Workbooks.Open FileName:= _
    "C:\Documents and Settings\dr\My Documents\street.xls"
    'END CHANGE
    [/vba]


    I think that's it for now. Like I said this is a start.


    As always try this on a _COPY_ of your data.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login