List Validation Lookup Table Help Please

[Kenny]

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much

 

[Joel]

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

 

[Kenny]

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code

 

[Joel]

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

 

[Kenny]

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!