How can I create dependent & dynamic DropDown lists that start with a certain string?

Hello,

Before I start describing the problem, please note that I have posted this same question on another forum, here is the link:
http://www.mrexcel.com/forum/excel-q...ml#post4634209


I would like to create 3 levels of dependent & dynamic DropDownlists (Data Validation lists) based on the following ranges:

DropDownList Levels-Ranges.png


The 3 ranges are created as: Level_1, Level_2, Level_3.

- DropDown list 1 must display items from range Level_1.
- DropDown list 2 must display FILTERED items from range Level_2. Example: if "1.ACTIVE" is selected on DropDownList 1 then the available items on DropDown 2 should only be: "1.1.ACTIVE CURRENT" and "1.2.ACTIVE NON CURRENT". The formula for the Data validation on DropDown 2 should filter the Level_2 range by the first 2 chars selected on DropDown1, in this case it should return a list of items that start with "1.", which are items 1.1. and 1.2.
- DropDown list 3 must display FILTERED items from range Level_3. Example: if "2.1. PASSIVE CURRENT" is selected on DropDownList 2 then the available items on DropDown 3 should only be: "2.1.1..." and "2.1.2. ...". The formula for the Data validation on DropDown 3 should filter the Level_3 range by the first 4 chars selected on DropDown2, in this case it should return a list of items that start with "2.1.", which are items 2.1.1. and 2.1.2.

I have tried with several OFFSET formulas and some worked, but unfortunately the OFFSET formula on the DropDowns stops working when you close and re-open the excel file. That's why I'm asking for your help, perhaps there is another way to achieve this?

Your help will be greatly appreciated.

Mc

Something Like this? See formulas in data validation.