Combo Box list based on value in another cell

[ninad7]

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.

 

[Kevin Lehrbass]

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

 

[ninad7]

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.