Define name, Data Validation, and drop down menus

**zhangke8280** · 02-11-2015, 02:42 PM

Hi guys I am stuck here, was wondering if anyone can help me. Basically, I have 3 different categories, plan codes, states, and *** code. Each plan code could have more than one states and *** code associated. such as plan code PL could be All but MA, and all but NY. Also for the ***, it could be both male and female. The problem comes to I want be able to create a drop down meu for plan code, state, and *** code. So when I select a plan code, PL, then when I select state, only All but MA and all but NY will appear, and after I select the state, only the available *** will appear. What's happengin now is every time I select a plan code, all the different states, such as MA, NY, all but MA, etc will appear, and I have to look into my plan book, to find out which one is the correct one, same goes for *** code. I know I could use define name, and define maybe All but MA and all but NY as PL, and do it that way. But I have 350 plan codes, and it could be a lot of work. The reason I want teh drop down menus, is because I have formulas down teh page to calcualte rates based on plan code, state, and ***. It would be nice if once I select a plan code, only the assoicated states and *** will appear, instead of every state and *** code. Is there anyway to do this? Thanks in advance. http://imgur.com/0AQiWB7
p.s. What I did in the past was create 3 columns with all the plan codes, states, and *** code, and choose each column as one of the data validation.