|I’m using MS Excel 2003 SP3.|
My user has asked for a two column combobox in a regular spreadsheet list in order to lookup location codes to store in a column on each row. She wants to be able to see the name associated with the location code, so she does not have to refer to a separate list.
The reference data look like this, and there are 199 rows:
001 Commissioner and Board
002 Special Operations
003 Communications Center
004 Public Affairs
005 Budget Services
I have tried implementing this by placing a combobox at the top of the workbook and freezing the panes, so it is always visible. I’ve gotten the combobox working correctly, populated, and linked to a cell.
*** The problem is that I can’t figure-out how to send the selection from the combobox to different rows – the cell link is hard-coded as a setting on the combo.
One solution would seem to be placing a combo in each cell of a column, but there are too many rows and each combo would have to be reprogrammed.
Another possible solution would be through Data Validation (i.e., right-click on the cell and select “Pick from drop-down list…,” but that method appears not to allow a drop-down having more than just one column, when we need two columns.
Another thing I’ve considered is sending the selection to ‘the previous cell that was current,’ (like Screen.PreviousControl), but I don’t know if that is possible in Excel.
Another possibility would be if I could set the ‘cell link’ of the combo to something relative, like ‘store it in the cell just to the right of this one.’
I would appreciate any help I can get.
I am good with VBA in MS Access but not so much in Excel, so I could handle a code solution.
Thanks a lot.