Dynamic update on ComboBox

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I created a list of item B2:B22 in a page called "Notes" and then linked a
Combobox from page1 to that list of items.
that works great, but I want the list to be Dynamic, so when I add an item
to the item list it would reflect that addition in the ComboBox. I have
typed in the "input Ranges" Notes!$I$2:$E$100 to get more fields but that
shows me a to many empty lines in the combobox!

And Again Thanks for all your Help!

Kevin Brenner
 
You should be able to use dynamic ranges to deal with this.

http://www.cpearson.com/excel/named.htm#Dynamic

On the MenuBar, Insert > Name > Define.

1. Place your name in the top TextBox.
2. Copy the formula below into the RefersTo box. (Use Ctrl-V to paste)
3. Hit OK.
4. Then, back to 'InputRanges' for ComboBox and Enter the name that
you chose.

(Modify this to suit)

=OFFSET($B$2,0,0,COUNTA($B$2:$B$500),1)

HTH
Paul
 
Assign a dynamic range to a range name (Insert/Name/Define)
=OFFSET(notes!$I$2,0,0,COUNTA(notes!$I:$I),1)

Use the name in the combo 'refers to' box.


Regards
BrianB
 
If your combobox is from the controls toolbox toolbar and located on a
worksheet, then you will probably be disappointed in the results. You would
need to update your range using code in the change event if you want updates
to timely. If a dropdown from the formst toolbar or using Data=>Validation
with the list option, then dynamic names work well.
 
Back
Top