populating cell with exact value from drop down list

[00ryan00]

I am using the form control data validation drop down list with 9 entries in the list. How can I get the entry that is selected in the drop down list to populate exactly into another cell on a separte worksheet? I have linked the drop down list to the cell, but the value is populating as a number rather than the text entry that is in the drop down. Help.

[moinsk21]

Hi,

Please find the example sheet.

[rcm]

Suppose that the drop down list is in B2

in the desired cell use =TEXT(b2,"#######")

[rcm]

Disregard post #3, what the cell is giving you is the index number of the entry. One way is to indirectly reference the list using the index as shown in the workbook below

[Richard Buttrey]

Hi,

Let the control populate the number (which is the item index on the lookup list) in a helper cell say B1 and then use

Please Login or Register  to view this content.

[00ryan00]

Richard, I used the formula, but I think it needs to be tweaked to my particular case. Can you explain what the entries in the formula above correspond to?

[Tony Valko]

Sounds like you're using a combo box. Is the drop down arrow always visible? If so then it's a combo box.

A combo box returns the index number of the selected item to the linked cell.

If these are the selections in the combo box:

Tom
Lisa
Karen
Bill
Sammy

And you select Karen, the linked cell will display the number 3 because Karen is the 3rd item in the list.

To get the actual value of Karen in a cell you would have to use a formula that looks at the source for the combo box and "finds" Karen.

Where is the source for the combo box and where is the linked cell?

[Richard Buttrey]

The Index() function returns the value that is at the row and column co-ordinate of the index range.

The first of the three parameters of the INDEX function is Sheet1!A2:A5 where you keep your list of values for the drop down box, the row co-ordinate in this range is cell B1 value, and the column in the A2:A5 range is column 1, i.e. A2:A5. (Note in other situations the index range might be say A2:Z10 and hence you could pick up any of the column values by specifying a column from 1-26).

The B1 value is the numeric value returned by your choice in the drop down box.