I have a drop down list generated in Excel from row values. I want to select from that drop down, get the index of that value in the drop down array, then use that for an offset value to select the value from a different column.
I can't seem to find a simple way to get the index of the selected value in the drop down.
Every answer I find makes assumptions about where the array is generated, but here they are generated by different sets of data that can be changed at any time.
Best Answer
you don't need VBA to resolve this.
Define a named range in the workbook for the list you are using for the validation (In my case I have
<5
,>5
and>10
in cellsA2
,A3
&A4
for Years of Service, so the named range is calledYears_of_service
.Validation is in cell
C1
using the defined name range ofYears_of_service
for the list.D1
to work out the offset or the item selected in the list is=MATCH(C1,Years_of_service,0)
.Mark O'Rafferty