Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KPro)

[steve_skelton13]

I have an excel spreadsheet with several drop-down boxes on worksheets in various places. I want to link a cell on another sheet to the Value displayed in a Drop Down box on another sheet - NOT the Cell Link but the actual selected contents of the drop-down box. I'd like to avoid doing some kind of VBA Select Case as some of these drop-down boxes have a lot of entries (for example, every county in Texas). Can't seem to find the right magic - even to reference a Drop-Down box. I know you can do this on a Form using the index of the objects but I'd rather not do forms at this point as I want things to be very easy to deal with and a pop-up form is a complication.

what i'm looking for is something like <font color=448800>='Worksheet'!'Drop Down 2'!Value</font color=448800> plugged directly into a cell.

can this be done? thanks in advance...

[HansV]

Create a two-column table somewhere (it may be in hidden columns or on a hidden worksheet if you like). Put the index values of one of the drop down lists in the first column, and the list entries in the second column. Now, you can use a formula with VLOOKUP to get the list entry from the linked cell:

=VLOOKUP(linked_cell,lookup_table,2,FALSE)

[steve_skelton13]

it's a combo box from the Forms toolbar set directly on a worksheet. i tried data validation using List and tried to reference the list values used for the drop down box on another page but it doesn't do anything - it seems to want to look for a list of values on the page holding the data validation cell, which is not optimal for what i am trying to do.

thanks for the quick response! i'll keep working on it.

[SammyB]

Not sure what you mean by drop-down box. Is it a ComboBox from the Forms toolbar or an Active-X ComboBox from the VBA Control Toolbox? It would be easier to use Data Validation. Look at the attached workbook. No VBA code, no controls. After clicking on the yellow cell, use the Data | Validation menu to see how to set it up. HTH --Sam

[SammyB]

Yes, the list of values must be on the same sheet, which is a pain, but if you use a white font as I did (edited the post above and did the attachment) it works fine.

[SammyB]

See attached to do it your way. You just right-click to select the combo, choose format control, and set-up the linked cells. It returns an index, which you can hide. I used the INDEX function to get the name of the county. I still like Data Validation better, but it is nice that you can make the drop-down list longer with your way. HTH --Sam

[steve_skelton13]

Thanks! works fine. I put the referenced cell for the INDEX function on sheet 2 and it worked just fine.

[steve_skelton13]

I was having problems getting VLOOKUP to work but another lounger suggested INDEX and that seems to do the trick.

thanks for helping!

[Servando]

Sam

you can use Data|Validation (List) using data of another sheet, but in the source you must declare a name defined and not the range of cells.


Source:
=TexasCounties

[SammyB]