How to create two validation lists that modify each other's value?

[Chracatoa]

For instance, let's say I have a list containing both product numbers
and descriptions. I would like to create the following validation
lists

Number Description
[12345] [Digital Camera XBC]

Original lists:

00001 Camera
00002 Mouse
....
12345 Digital Camera XBC
....

If I know the product's number, I select its number from the list on
the left and it automatically changes the description. If I know the
description, I select the description and it automatically changes the
product's number.

Any ideas?

 

[Tom Ogilvy]

You could use a Vlookup to do one or the other, but you can't have both a
formula and a value in a cell. You would need to use the Worksheet_Change
Event to lookup the value you don't enter.

In Excel 97, selection of a value from a data validation dropdown does not
fire the change event, so this wouldn't work in xl97.