Combox - Can I make it pull from a function (or use some othertrick?)

[Andrew Backer]

I have written a nice little function that returns a list of values
that i would like to use in a combo box. So far, It seems I can only
pull from a named range for the values.

The background:
The contents of the combo need to vary depending on the value in the
box next to it. Basically, we have a big lookup table and we use the
value to the left of the combo box to filter that data through a
custom function. The function comes in because we need to remove
dupes, blanks, and clean it up somewhat.

Is there any way, even a round-about way, to get it to use the
function? Some kind of temporary named range on another page? A way
to use another function inside the named range formula to maybe
determine which range on another sheet to use (and the calling of the
function could force a recalc?)

I am open to an alternate approach, but I still need a validating cell
that is either a dropdown, or has some form of autocomplete!

Thanks,
//Andrew

 

[Matt Richardson]

I have written a nice little function that returns a list of values
that i would like to use in a combo box.  So far, It seems I can only
pull from a named range for the values.

The background:
The contents of the combo need to vary depending on the value in the
box next to it.  Basically, we have a big lookup table and we use the
value to the left of the combo box to filter that data through a
custom function.  The function comes in because we need to remove
dupes, blanks, and clean it up somewhat.

Is there any way, even a round-about way, to get it to use the
function?  Some kind of temporary named range on another page?  A way
to use another function inside the named range formula to maybe
determine which range on another sheet to use (and the calling of the
function could force a recalc?)

I am open to an alternate approach, but I still need a validating cell
that is either a dropdown, or has some form of autocomplete!

Thanks,
//Andrew

I've attached a possible way that it could be done, where you can use
HLOOKUPS and their results to change the values in a named range and
therefore change the contents of the ComboBox. Is this the kind of
thing you're after, or am I barking up the wrong tree?

File is here:-

http://www.evalu8-it.com/Dynamic Dropdown.xls

HTH

Mat Richardson
http://teachr.blogspot.com