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

Topic

New Reply

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 =’Worksheet’!’Drop Down 2′!Value plugged directly into a cell.

can this be done? thanks in advance…

Reply | Quote

Replies

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)

Reply | Quote

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

thanks for helping!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Reply | Quote

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

Reply | Quote

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

Reply | Quote