hi, i have a problem...

i have a list box and 5 dynamic named ranges. i also have a series of 5
option buttons that control which of the 5 named ranges is the one i
want.

what i want to do is have the Input Range for the list box dynamically
update with the named range chosen via the option buttone.
unfortunately, it seems the the contents of the Input Range must
actually be a defined range and cannot be a formula (indirect,
address), and also cannot be a reference to a cell which contains the
named range as its value.

any thoughts on how to accomplish this?

thanks


--
jruppert
------------------------------------------------------------------------
jruppert's Profile: http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread...hreadid=478096

Hi!

Try this:

Assume your 5 named ranges a Rng1, Rng2, Rng3, Rng4, Rng5

Link the option buttons to a cell, say, B1.

Create this named formula and give it the name of, say, Rng

=CHOOSE($B$1,Rng1,Rng2,Rng3,Rng4,Rng5)

Now, for the input range of the list box enter =Rng

Biff

"jruppert" wrote in
message ...

hi, i have a problem...

i have a list box and 5 dynamic named ranges. i also have a series of 5
option buttons that control which of the 5 named ranges is the one i
want.

what i want to do is have the Input Range for the list box dynamically
update with the named range chosen via the option buttone.
unfortunately, it seems the the contents of the Input Range must
actually be a defined range and cannot be a formula (indirect,
address), and also cannot be a reference to a cell which contains the
named range as its value.

any thoughts on how to accomplish this?

thanks


--
jruppert
------------------------------------------------------------------------
jruppert's Profile:
http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread...hreadid=478096

i will give that a try and let you know, thanks!


--
jruppert
------------------------------------------------------------------------
jruppert's Profile: http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread...hreadid=478096

AWESOME, it worked!

thanks a lot!


--
jruppert
------------------------------------------------------------------------
jruppert's Profile: http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread...hreadid=478096

You're welcome. Thanks for the feedback!

Biff

"jruppert" wrote in
message ...

AWESOME, it worked!

thanks a lot!


--
jruppert
------------------------------------------------------------------------
jruppert's Profile:
http://www.excelforum.com/member.php...fo&userid=3170
View this thread: http://www.excelforum.com/showthread...hreadid=478096