MATCH formula

D

dah

I have a spreadsheet that lists all the types of machines and thei
model numbers. When a customer places an order for a specific machine
I need to reference the other workbook in order to get the model number
Is there a way to have this done automatically. For example: when
enter a machine type (CE-10), I want the model number for that machin
to be entered automatically in another cell, without having to open th
other workbook and copy and paste the number myself. I considered dat
validation (using a list) but can't reference another workbook.

Any thoughts?

Thanks,
De
 
F

Fable

Hi Deb why don't you hyperlink your validate data to the sheet you
working (setup your updates on automatic, less fuzz).

Or you could try a VLOOKUP formula which a bit more complex and i
basically the same thing.

Hope this helps
 
B

Bill Martin

I have a spreadsheet that lists all the types of machines and their
model numbers. When a customer places an order for a specific machine,
I need to reference the other workbook in order to get the model number.
Is there a way to have this done automatically. For example: when I
enter a machine type (CE-10), I want the model number for that machine
to be entered automatically in another cell, without having to open the
other workbook and copy and paste the number myself. I considered data
validation (using a list) but can't reference another workbook.
Click to expand...

First of all, here's how to reference a cell B2 from another workbook file
named "Test1". =[Test1.xls]Sheet1!B2

Now let's say your cross reference table exists in file Test1.xls, on Sheet1
from cells A1 to B5, AND let's assume you have it sorted into ascending order
of machine types in column A. Then if your spreadsheet has a machine type in
cell C7 and you want to automatically pull in the model number to C8, then
into C8 enter the formula:

=VLOOKUP(C7,[Test1.xls]Sheet1!A1:B5,2,FALSE)

This little toy example works in my Excel, but obviously you'll need to
change around your file names, sheet names, cell names, etc. Try to make a
toy version work before getting too deeply into your full problem.

Good luck...

Bill -- (Remove KILLSPAM from my address to use it)
 
Top