Combo Box populate from range

[ioustinos]

Hi there,


I have managedto populate the values of a combo box from a range of cells.

However i would like to be able to have my client (who is going to be using this excel), to be able to add options, without having to go to the combo box and reset the range. Just by adding one entry under the cells that allready contain the available values.

Is this possible?

thanks

[ConneXionLost]

Yes.

You must ensure you set up your combo box to use a "named" range, then you can modify it to become a "dynamic" named range.

For example, suppose your combo box has this as it's source:

=$A$1:$A$10

First, select the range $A$1:$A$10, then put a name in the "Name Box" (upper left corner), like "picklist".

Then, go to the Data Validation dialog for the combo box and designate the source as:

=picklist

Click "OK" (ensure the combo box is working with the named range).

Now, go to "Insert > Name > Define..." and select "picklist". In "Refers to..." change the reference to this formula:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

then click "Add", then "OK".

Finally, check the results by adding an option to your list and see if it turns up in the combo box.

Cheers,