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
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

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

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
----------------------------------------------


"Kevin" wrote in message ...
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

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.

--
Regards,
Tom Ogilvy

wrote in message
...
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
--------------------------------------------------------------------------
------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------
------------------------------------

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

Thank you guys the data validation formula worked very nice. Thanks

Kevin Brenner

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

wrote in message
...
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

--------------------------------------------------------------------------
------------------------------------
Be advised to back up your WorkBook before attempting to make changes.

--------------------------------------------------------------------------
------------------------------------

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