List Validation Lookup Table Help Please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kenny - 30 Sep 2007 19:38 GMT

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

Reply to this Message

Joel - 30 Sep 2007 21:30 GMT

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.

> 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
[quoted text clipped - 13 lines]
> please tell me what i need to put in the code list validation bax, thanks so
> much

Reply to this Message

Kenny - 30 Sep 2007 21:57 GMT

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

> You have to use match. If the table starts in row 1 then
> =MATCH($A$1,$A$2:$A$100)
[quoted text clipped - 23 lines]
> > please tell me what i need to put in the code list validation bax, thanks so
> > much

Reply to this Message

Joel - 30 Sep 2007 22:17 GMT

did you include the sheet number as below

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

> this does not work, the list validation needs to refernce sheet2 not sheet 1,
> i get an error with your code
[quoted text clipped - 26 lines]
> > > please tell me what i need to put in the code list validation bax, thanks so
> > > much

Reply to this Message

Kenny - 30 Sep 2007 22:48 GMT

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!

> did you include the sheet number as below
>
[quoted text clipped - 30 lines]
> > > > please tell me what i need to put in the code list validation bax, thanks so
> > > > much

Reply to this Message