combo box input range based on the other combo box selection

**hedayet** · 04-04-2013, 07:38 AM

Combo_Box Test.xlsx

Hi,
I wanted to select named ranges in the combo box 2 based on my combo box 1 selection. I have used _STableName=INDEX(T_SectList, SectPt,5) to return the desired table name from sheet "Mat Data". With this formula combo box 2 displays the name of the range (e.g. T_S_RHS350) not the contents of that range.
Can it be done without VBA? If not what will be the simplest codes to start with? I have very limited knowledge of VBA.

Thanks in advance for your help.

Regards.
Hedayet

**AndyLitch** · 04-04-2013, 08:10 AM

Try this.. Hope the explanation is ok

1.The list for the top drop down is taken from cells I3:I6

2.The selected value for the top drop down is in I2

3.The functions in P3:P21 extract the selected list from the 4 red lists using the offset function and the value in I2
=IF(OFFSET($J$3,ROW(G3)-3,$I$2,1,1)=0,1111,OFFSET($J$3,ROW(G3)-3,$I$2,1,1))

4. The lower dropdown is populated by a dynamic named range called ListB which takes the true list values from column P.
=OFFSET(Calculation!$P$3,0,0,19-COUNTIF(Calculation!$P$3:$P$21,"=1111"),1)

Answer Combo_Box Test.xls

**hedayet** · 04-07-2013, 11:02 PM

Hello Andy,
Thanks very much for giving me a nice and simple solution. However, I am still struggling to implement this in my cases where my range tables are not in the column next to each other as your example. In my case I have more than 20 data tables placed one below another. Is it still possible to use OFFSET function?
It would be easy for me if I could use the "range names" somehow in the 2nd combo box input range. It seems I have no other choice but VB codes.
Thanks again for your help.
Hedayet

**AndyLitch** · 04-08-2013, 02:27 AM

Yes it is.. Offset will work but the arithmetic gets slightly more complicated...You need to scale your row offset by the separation between the tables