Hi there,

I want to do a simple "foreign key reference" in Excel. I have a
worksheet storing product information, which has a "product ID" column
and a "product Price" column. We often need to create new worksheets
for quotations. In the new worksheet, I will type in the product items
our customers want, and I want a quick and easy way to fill up the
price column in my quotation worksheet. It shhould work very similarly
to a database foreign key reference.

Some special features in my case:
1. We prefer not to create a database to do this
2. The "product information" table is not accurate, and the "product
ID" column cannot be a true primary key column. For example, products
coming from different suppliers may have the same product ID; and
different workers may use slightly different product IDs, such as
"MS-1001" vs. "MS 1001" vs. "MS1001". If the solution can be in a way
intelligent to recognize this, it would be great.
3. We don't want to make the solution over-complicated, such as
spending thousands of hours to develop an application for this.

Your generous help is appreciated.

Thanks,
Richard

the built in lookup functions such as vlookup can do this with a standard
database paradigm. If you want to make it intelligent, you can put in a
dummy column that contains a formula to standarize entries as intelligently
as you want to create it. (include concatenating more than one column to
make a unique key)

You can also look at a combination of the Index and match functions. See
help for details.

As for duplicate entries, again, you can use your dummy column to make them
unique. It will be as intelligent as you want to make it.

--
Regards,
Tom Ogilvy

noname wrote in message
om...
Hi there,

I want to do a simple "foreign key reference" in Excel. I have a
worksheet storing product information, which has a "product ID" column
and a "product Price" column. We often need to create new worksheets
for quotations. In the new worksheet, I will type in the product items
our customers want, and I want a quick and easy way to fill up the
price column in my quotation worksheet. It shhould work very similarly
to a database foreign key reference.

Some special features in my case:
1. We prefer not to create a database to do this
2. The "product information" table is not accurate, and the "product
ID" column cannot be a true primary key column. For example, products
coming from different suppliers may have the same product ID; and
different workers may use slightly different product IDs, such as
"MS-1001" vs. "MS 1001" vs. "MS1001". If the solution can be in a way
intelligent to recognize this, it would be great.
3. We don't want to make the solution over-complicated, such as
spending thousands of hours to develop an application for this.

Your generous help is appreciated.

Thanks,
Richard