Drop down menus

[sweetthing4luc]

I am turning to the forums for help again; I have been assigned a task which is becoming a nightmare. This is the situation, I have tried numerous times and for some reason I am doing something wrong or the Microsoft web site is not explaining it clearly. I have a list of Insurance Companies, and their multiple plans that they offer. I want to create a worksheet that can help me do the following. If I were to select one of the Insurance Company, I would like a list of only the products that the company has to offer, not every ones else plan. Then I would like to be able to choose the plan that I want and have the specifications pop up into certain fields. I know I need to create a master worksheet with all of the Information on one and have another worksheet as my form. If you can please help me this would be greatly appreciated. I am just really confused on this, and I need help. Thanks so much.

[JBeaucaire]

This is pretty straightforward stuff. Postup a mock sample of your data with a couple of companies and some products for each in your "database"...and then mockup what you want the "form" to look like, we can set up the drop boxes and make the form display as desired.

[sweetthing4luc]

For example: I would first like to choose from a company

Company A
Company B
Company C
Company D
Company E

Than from that company say we chose “Company C” we can choose a plan that they offer, say they offer

Plan 1
Plan 2
Plan 3
Plan 4
Plan 5

Then once we chose the pan say we choose “Plan 3”, then I would like all of the detail of Plan 3 for only Company C to show up in specified cells such as.

Plan Name
Deductible
Office Visit Amount
X-Ray and Lab

And so on, there are about 50 different companies, and each company has about 50 different plans, so giving actual detail is going to get bad, but, if I can get the basics on how to do it, this would be greatly appreciated. Please let me know if you need me to provide anything else.

[JBeaucaire]

The first two are going to be handled with a Dependent Drop box. Here's a sheet that shows how to what you're looking to do.

[sweetthing4luc]

Thanks so much for helping me with my problem with the drop down menu. I got your document and understand how to do the drop down boxes and how to validate them. But, when I try to change the information in sheet 3 for the additional information I cannot get it pop up, it remains blank. Is there a step that I am missing, please let me know when you get a chance. Thanks so much for your help again.

[JBeaucaire]

Post up your sample with errors pointed out, I'll try to see what's wrong. The database page is pure listing. The formulas on the "report" page in rows 11+ are using a standard INDEX/MATCH approach, so this should just be working if your entry in B1 matches one of the options in column A on "database".

[sweetthing4luc]

Here is the attachment

[JBeaucaire]

You needed to create a Validation list for the names of companies. I did that using range A1:O1 on the sheet Lists, so any company name you put in row 1 will appear in the list of companies.

Then I created a named range from row2 (need that blank space) to row 20 as the Named range for each company. This allows you to expand the Plans under each company for a while.

NOTE: United Health Care is a company with "spaces". You need to remove the spaces for the named range, I named the range UnitedHealthCare.

I used a INDIRECT(SUBSTITUTE) to take the spaces out in the data validation list so it can handle the Company names with spaces in them. Look at the medical validation list in cell C9.

Then a standard INDEX/MATCH formula brings over the data for the chosen plan. You MUST ensure the Plan names are unique column A of the Database. If Regence and Aetna both have a Plan A, adjust then name in some way so they are unique. As long as the plan name in column A matches the plan name from the Lists page, it will be found by the INDEX/MATCH formula.

You will need to extend the Data Base sheet to include all the columns for the Dental and Vision options, too. The data base can hold it all since you're going to irganize it into the comparison sheets in a meaningful way.

I completed the first column of Medical (1) so you can see the whole thing in action. It will work fine. I suppressed the errors in row 36 as well.

In the first drop down box, all of the items have to be shown with NO SPACES. You can't name another range of cells "United Health Care" because that's got two spaces. You would need to

[JBeaucaire]

As per the PM, you're very welcome, I'm glad it's clear and working now.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[JBeaucaire]

As per PM inquiring how to include more "plans", only 22 of the 32 are showing up in the drop box...this is a function of the NAMED RANGEs.

Press CTRL-F3 to open the names definition window.
Click on Regence
Adjust the "Refers to:" range to go further down the column, perhaps to row 50.
Click ADD to save your change
Repeat with other company "names".

[sweetthing4luc]

Here is the attachment

[sweetthing4luc]

Sorry I didnt get the message until after I sent it to you, I will post the questions on here.

[sweetthing4luc]

Sorry, I didnt get your message before I sent you the private message. I will post all questions here in the forum. Please forgive me.

[sweetthing4luc]

Nevermid, I was able to figure it out, thanks so much for your help. Everthing is good to go and alright. I will state this is "Solved" Thanks again

[sweetthing4luc]

Okay I need to re-open this question again. I have been using everything okay, but now when I got to check on (Medical 1) to make sure the plan summaries are popping up, only some of the information is popping up. Is there something I can do to fix this. I will post what I have.

[JBeaucaire]

You've made the same mistake again. The NAMED range you create has to match the Name of the company minus spaces.

You created a company named AET (United Health Care) but then you named the range of plans below is AET. That's not correct. You can't use parentheses either. So, AETUnitedHealthCare is the name, and you'll have to change the Data Validation formula in the medical pages to this, for C9:

=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C8," ",""),"(",""),")",""))

[JBeaucaire]

My my my...ok, two things.

First, remember you can only name NAMED ranges with letters, upper and lower case. So, every company name you create has to have a named range below it that strips out "characters".

AET (United Health Care) = AETUnitedHealthCare
WAHIT (Premera) = WAHITPremera
Thompson & Spears (Regence) = ThompsonSpearsRegence

Get the idea? No exceptions.

Also, I've spotted the following characters so far to be removed:
(
)
&
(spaces)

So this is the data validation formula for the cells to display the plans from each company:

=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C8," ",""),"(",""),")",""),"&",""))

If find any new characters are introduced (like dashes or periods or anything, you'll need to keep expanding that substitution list.

[sweetthing4luc]

Great!!! I got it, Thank you so much, I am saying that this issue is finally solved.