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

  • Thread starter Thread starter Andrew Backer
  • Start date Start date
A

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
 
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
Click to expand...

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