Using data val. drop-down list to select offset

[R.Walters]

I'm using MS Excel '02 w/SP3 to make a spreadsheet that automatically
fills in certain fields based on a model number that is selected from
a drop-down list created using the data validation method. What I
would like to do is have a cell that reflects the index value of the
item selected from the drop-down list. That index value is then used
in an OFFSET formula that fills in the fields.

As an example of what I want to do:
I pick the second item in the drop down list;
this stores the number 2 in cell "C1";
the formula =OFFSET($A$1,$C$1,1) is entered into "D1";
the cell "D1" displays the value present in cell "B3";
if I were to pick the first item in the list,
"D1" would display the value present in cell "B2";

I have been able to make this work using a control box, but I can't
figure out how make it work with a validation list and have spent the
last hour and a half sifting through archives on this and other
forums. If you know how to make a validation list insert an index
value into another cell, please reply.

 

[Frank Kabel]

Hi
the data validation listbox can not be configured this way. You may add
some code to convert the entered value to the list position but maybe
you could also use a VLOOKUP formula in cell D1.
You may provide some more details:
- what is the location of the source list
- which value (from which range) do you want to show in D1
- how are both values realated

 

[Debra Dalgleish]

Assuming the dropdown values come from a list (named MyList) on the
worksheet, use the following formula in cell C1:

=MATCH(H1,MyList,0)

where the dropdown list is in cell H1.
If H1 is empty, the result will be #N/A

 

[R.Walters]

Thanks a lot for the replies. Using the MATCH property was just what I needed.

-Ryan