Data Validation based on Condition in Excel

Question

Code	Desc
10100	AA
10200	AB
10201	AC
20200	BA
20100	BB
20203	BC

Hey guys I was wondering if this is possible using only formula in excel.

I have two dropdowns. The first dropdown contains: Class A Class B

And in my second drop down if Class A is selected then the list in 2nd dropdown would be all those with Code where '1' is the first character + their Desc.

and if Class B is selected the 2nd dropdown would list all those with Code where '2' is the first character and the 3rd character is also '2' + their Desc.

For example if I select Class A in my first dropdown the choices on my 2nd dropdown will be:

| 10100 AA |

| 10200 AB |

| 10201 AC |

and if Class B is selected then the choices will be:

| 20200 BA |

| 20203 BC |

What I have tried so far is I created a formula to separate the two into Class A and Class B and then use =indirect(). But as the list gets longer the size of the excel file also increased and it is also taking a long time to load. And every time new data is added into the list I also need to change the range that is in reference.