• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combo Box list based on value in another cell

ninad7

Member
I have a combo box called "cmbTotal" (ActiveX control and not Forms) with list values 0,5,10,15,20,............100 which is a range named Scores. Based on a cell "A1" with range name TrendLimitValue which can take value from 0,5,10,15,20,............100, I want to limit the choice the list of the combo box named cmbTotal.


So for e.g. if "A1" is 15 then cmbTotal list should show 0,5,10,15. If "A1" = 25, then cmbTotal list should show 0,5,10,15,20,25 only.


Any help...............


TIA.


Ninad.
 
Hi Ninad,


I tried attaching a dynamic named range =OFFSET(Sheet1!$B$2,0,0,MATCH(Sheet1!$A$1,Sheet1!$B$2:$B$22,0),1)

to the combo box.

That didn't update after changing cell A1.


So, in cell D2, I added this formula =IF($A$1<B2,"",B2) and dragged it down.

I created a 2nd named range and attached it to the combo box (instead of the original).

This works (you can change cell A1 value to shrink or expand list) except that you'll have some blanks in your combo box drop down list.


I believe there may also be a possible vba based solution (macro code to change the height of your drop down list).


I hope this helps!

Cheers,

Kevin
 
Thanks for the response Kevin. I'll try it out and post a feedback in a couple of days. The blanks I can live with as long as they cannot be selected.


Have a great 2011. Cheers.


Ninad.
 
Back
Top