coding

[Guest]

I have two pages in a workbook. In page one I have lists in cells that are
validated by cells in page 2.
What I would like is, next to the cell that is validated, have a cell that
has a relevant code eg. If cell A1 has a list box that has fruit names in
Apple, pear etc (Validated from A1 in page 2) I would like B1 in page 1 to
have a code for each (Apple = 1, Pear = 2)
So that if a user chooses Apple from the drop down 1 will appear in the next
cell, if they change it to Pear from the list 2 appears etc.

 

[Max]

Think a combo box (from the forms toolbar) could give you the desired
behaviour ..

Try this simple set-up

In Sheet2
----
List down in A1:A3 : Apple, Pear, Orange

In Sheet1
----
From the Forms toolbar*
Click on combo box, then draw a combo box over cell A1
(hold down the ALT key when drawing to make it snap to fit to grid)

*Click View > Toolbars > Forms to activate the toolbar if necess.

Right-click > Format Control

Enter for
Input range: Sheet2!$A$1:$A$3
Cell link: B1
Click OK

Try out the combo box:
Select Apple, B1 returns: 1 (as Apple is the first item in the list)
Selecting Pear returns 2, selecting Orange returns 3, and so on