Excel 2007 : Conditional Summary of Prices

[longfisher]

I sell group health insurance benefit packages which often contain no merely the base medical plan but also can contain a pharmacy, dental, vision and life insurance component the option of the customer. Furthermore, there are various choices of plans amoung the major categories of insurance. For instance, there might be 5 health plan options, 5 dental plan options, 4 dental options, 3 vision options and 5 life options.

I have a spreadsheet with 4 tables (1-4) that are 5x5 cells each and each table has a header row. Each table represents a different type of insurance, medical, dental, vision and life in tables 1 - 4, respectively. Each cell in the header row contains the name of a particular insurance plan within that type. The left column contains names of classes of employees (4 classes). The the body of the table contains dollar amount premium rates which someone in a particular class would pay for the insurance plan named in the header row of each table. Simple enough, right?

I would like to be able to simultaneously select one plan name in the header row in each table and once a selection is made in each table have the rates for all chosen plans summed for all four classes of employees.

The idea here is to allow an employer to try various combinations of insurance plans and have the spreadsheet quickly calculate the amounts that would be withheld from the pay of each member of a particular class depending on the combination of plan selections made. Furthermore, the spreadsheet should allow those choices to be quickly and easily changed so as to view the cost impact of a new combination of plans.

One thought I had was to select the desired combination of plans by holding downthe CTRL key and clicking them. Don't know how to make that work.

Another idea was to turn the plan names into buttons of some sort and clicking them would select the plans. Dont' know how to make that work either.

Lastly, although I've done some programming in my life, I've never learnd VB. And, I don't enjoy the luxury of time to learn it, really. So, non-VB solutions would be preferred.

I've attached a copy of the spreadsheet I'm working on. Any suggestions would be appreciated.

LF

[FDibbins]

If I have correctly understood what your needs are, the attached file will do what you need.

To use it, put an "x" on the options you want to select, and it will pull in the required value. At the bottom of the table (on sheet2) I have totalled the various options, and given an overall total.

I did not bother with the cosmetics, Im sure you can make it look however you want to

Hope this helps

[longfisher]

If I have correctly understood what your needs are, the attached file will do what you need.

To use it, put an "x" on the options you want to select, and it will pull in the required value. At the bottom of the table (on sheet2) I have totalled the various options, and given an overall total.

I did not bother with the cosmetics, Im sure you can make it look however you want to

Hope this helps

Thanks, FDibbins. That's thought Provoking and you came very close to what I need.

Only difference is that I need to just mark the plan name. The numbers below the plan name in the same table would then be values for subsequent calculations and those results would end up in the sum at the bottom.

Let me take a closer look at what you did though. I think it can be modified to suit my goals..

Best,

LF

[FDibbins]

Glad I could offer some thought. I tried to put the "selection" option into your main table, but seeing as its a "table" (which I havnt used before) it wont let me add extra columns