Seems to be a lot of these lately!
I've used [i]Data Validation[/i] combined with a [i]named range[/i] (in B24:B31) to give me a drop-down list of values (in cell D19). I need to refer to this value using VBA, which is as simple as referring to D19, but I also want to know the corresponding cell in that named range (eg B27 when choosing 'delta' from the list).
It would be even better if I could return the cell reference in (rows, columns) format.
The tedious solution is just to use lots of IFs in VBA, eg IF D19 = delta, var = B27 [or (2,2)].
I don't need to have the range named so could remove that if it helps.
INDEX ?
I don't really understand the question. You have a drop down which is linked to cell D19, and you want to know what the user has selected - not the actually 'thing' that was selected but the cell address of that 'thing'?
If so, just add the value in D19 to the number of rows above the named range, and put the letter B before it?
Or if the range might move in some ways in the future, use INDEX to turn the number in D19 into the 'thing', then use MATCH to find the row of the 'thing'?
Could you read your source range into an array variable at the start of your VBA procedure, then cross reference from there?