Not quite sure of the end game from your description, but here goes:-
If you are looking to use DV to return a value such as Dog / Cat / Monkey / Zebra etc, and then
have another value associated with that item appear in another cell, then simply use DV on the
list of items and then use a VLOOKUP in the other cell where you want the associated value. For
example, if you had your list of items in say J1:J10, and their corresponding values in K1:K10,
then use DV (Assume cell F1) with the list option on J1:J10, and then use a VLOOKUP in say F2 to
return the corresponding value as follows:-
=VLOOKUP(F1,J1:K10,2,0)
---------------------------------------------
If you are looking to get back both the item and value in the DV list, then you are going to have
change a formula somewhere. Assume as above that your data is in J1:K10. In cell L1 put =J1&"
"&K1 and copy down to L10. Now use DV on that range to return a list of say Dog 3 / Cat 2 /
Mankey 7 / Zebra 8 etc, BUT, you will not be able to work with the result. So, have the formula
that was looking at the value returned from your DV look at just a section of the data, eg if your
value was being returned to say F1, in F1 you would now have something like Dog 8, so whatever
formula was referring to F1 will now Bomb. Therefore instead of having the formula refer to F1,
have it refer to:-
=LEFT(F1,FIND(" ",F1)-1) for just the left side in text format, or
=--MID(F1,FIND(" ",F1)+1,LEN(F1)) for a numeric representation of the right part.
If the text / number part is the other way round, then use the following:-
=--LEFT(F1,FIND(" ",F1)-1) for just the left side in numeric format, or
=MID(F1,FIND(" ",F1)+1,LEN(F1)) for text from the right part.
