ComboBox for a range of cells

[Malka]

Anyone,

I have a list of 25 swimming events and a list of 60 swimmers who will each
swim in four different events. To make life easier I have created a combo
box (which to me is already a feat).

The data is in column A, rows 2-20. I’ve placed box itself in the B column.
It is one row high but when I click on the box, it shows me all the events
(which is preferable to a drop-down box).

The input box is A2:A20.
The cell link is D2:d20.

When I click on the combo box it will put the entry into D2. However, if I
go to D3 and click on the combo box and change my choice, it changes in D2
but remains blank in D3.

If I copy the combo box and place it the next line down and choose an
option, then the first line changes to what I’ve selected for D3 -- unless I
manually change the cell link to start at D3. Then it works fine.

The catch is that I would have to repeat this process 240 times (60 racers
times four events). Is there a way to automate this or alternatively, is
there a better way to get what I want done?

Thank you.

 

[Luke M]

As you've found out, combo boxes only link to a single cell. However, I would
recommend using Data validation. You could apply it to all your different
cells in one giant swoop. In your example:
Select cells D220. Data - Validation, List
=$A$1:$A$20

If you *really* feel like getting fancy, check out some of Debra's articles
on dynamic validation:
http://www.contextures.com/xlDataVal03.html

 

[Malka]

Thanks for responding so quickly, Luke. I can do the validation list;
however it limits me to eight lines. Thanks to your link, though, I found
instructions on how to create a combobox with data validation list. Haven't
got it to work yet but I'm trying.