Alicia,
I'm with Otto on this, but may be you something like this ...
- Assuming they can select from none to all of the options, you can use the
bits of a long to store those selected, provided there are less than 32
options

dim OptionsChosen as long

If opt1.value=true then OptionsChosen =OptionsChosen or 1
If opt2.value=true then OptionsChosen =OptionsChosen or 2
If opt3.value=true then OptionsChosen =OptionsChosen or 4

and you check if, say opt3 was selected with :
If OptionsChosen and 4=4 then ... whatever

- Or you could use an array
Dim Options(1 to 3) as boolean

options(1)=opt1.value
options(2)=opt2.value
etc...

NickHK

"Alicia" wrote in message
...
Oh helpful ones,

I need help organizing the Select Case I'm trying to build. It's
overwhelming me!

I am trying to have rates for Medical and Dental insurance self-populate
when you choose the options. So, A2 would be Medical rate for employee on
row 2 and B2 would be the Dental rate for EE on row 2.

Here are the various options:
MEDICAL:
Employee can choose:
Option 1, Option 2 or Option 3.

EE, EE/SP, EE/CH or Family (EE/SP/CH)

Employee is:

Salary, Commission/PT or GLR (different rates)

We "X" the box for what the employee has chosen (Options and coverage)
which
seems to me is a string. So that makes strOpt and strDep. How do I
declare
properly so that if there's an X in the Option 1 box that strOpt is = 1,
Option 2 makes strOpt = 2, etc.

Same thing with strDep (1, 2, 3 or 4). Is String the right choice?

If the EE is GLR, Salary, Commission and PT don't matter. However, if
they
aren't GLR, then Salary and Commission or PT have different rates

I have a separate sheet with named ranges (Opt1, Opt2, Opt 3 and Den) and
each one has the Dep status on the right column with the Sal, Comm/PT or
GLR
heading across.

I think I could build this with a whole bunch of If..then.. statements but
Select Case seems to be the way to go. I'm just having a hard time
wrapping
my head around it.

The last question is where do I put it so the rates pop up at the right
time. Is this an OnExit procedure from a cell?

I usually use VLookup but I think this is too complicated for a formula.
Any and all suggestions are welcome! If you can come up with a formula,
please do!

Let me know if you need more info.

Thanks,
Alicia

Alicia,
If you are looking to do this on a worksheet, then you should into the
OFFSET, VLOOKUP, HLOOKUP, MATCH functions.
Also, you may find it easier using the option buttons from the Forms tool
bar rather than fom the Controls toll bar, as they return numbers intead of
True/false values.

NickHK

"Alicia" wrote in message
...
Nick and Otto,
Okay, I can see your point about explaining clearer. Let me try again.

I have a spreadsheet called notes. For each person who sent in their
benefits, we indicate what they have chosen.

So, for medical, John Doe has a choice between Option 1, Option 2 and
Option
3. I have 3 boxes that we can put an "X" in to indicate if we enrolled
him
in one of these three options (r3, s3 or t3). So, if I was writing a
formula, it would be something like:

if r3 = X then intOpt = 1 otherwise if s3 = X then intOpt = 2 otherwise if
t3 = X then intOpt = 3 otherwise intOpt = 0.

If they have no option (all three options are priced differently), then I
want a3 (which is where I want the price of their medical insurance) to
remain blank. However, if they choose an option, then I need to validate
what coverage they have chosen. They can choose employee only, employee
and
spouse, employee and child or family. (EE, SP, CH and F in code). The
formula would be something like:

If y3 = X then intDep = 1 otherwise if z3 = X then intDep = 2 otherwise if
aa3 = X then intDept = 3 otherwise if ab3 = X then intDep = 4 otherwise
intDep = 0 msgbox "No Dep Chosen"

I then have to determine if they are Salary, Commission, PT or GLR.
Salary
has one price, Comm and PT have another price and GLR has another price.
I
have a column with an S or C to determine sal or comm. I have another
column
with FT or PT to determine if they are PT and I have another column with G
to
indicate GLR.

I'm trying to get all these variables to do a lookup on the following
table
if they are option 1. (There are 2 other tables for Option 2 and Option
3).

Option 1:
Dep Sal Comm/PT GLR
EE: 55.00 238.48 60.50
EE/SP: 334.03 517.51 199.50
EE/CH: 218.00 401.48 163.50
Fam: 411.93 595.41 257.00

So, I think this makes my choices:

Option (which chooses what table to look at)
Dep (which chooses what row)
Status (which chooses which column)

Does this help? I can send you a few sample rows of the spreadsheet if
that
would clear it up.

Thanks,



"NickHK" wrote:

Alicia,
I'm with Otto on this, but may be you something like this ...
- Assuming they can select from none to all of the options, you can use
the
bits of a long to store those selected, provided there are less than 32
options

dim OptionsChosen as long

If opt1.value=true then OptionsChosen =OptionsChosen or 1
If opt2.value=true then OptionsChosen =OptionsChosen or 2
If opt3.value=true then OptionsChosen =OptionsChosen or 4

and you check if, say opt3 was selected with :
If OptionsChosen and 4=4 then ... whatever

- Or you could use an array
Dim Options(1 to 3) as boolean

options(1)=opt1.value
options(2)=opt2.value
etc...

NickHK

"Alicia" wrote in message
...
Oh helpful ones,

I need help organizing the Select Case I'm trying to build. It's
overwhelming me!

I am trying to have rates for Medical and Dental insurance
self-populate
when you choose the options. So, A2 would be Medical rate for
employee on
row 2 and B2 would be the Dental rate for EE on row 2.

Here are the various options:
MEDICAL:
Employee can choose:
Option 1, Option 2 or Option 3.

EE, EE/SP, EE/CH or Family (EE/SP/CH)

Employee is:

Salary, Commission/PT or GLR (different rates)

We "X" the box for what the employee has chosen (Options and coverage)
which
seems to me is a string. So that makes strOpt and strDep. How do I
declare
properly so that if there's an X in the Option 1 box that strOpt is =
1,
Option 2 makes strOpt = 2, etc.

Same thing with strDep (1, 2, 3 or 4). Is String the right choice?

If the EE is GLR, Salary, Commission and PT don't matter. However, if
they
aren't GLR, then Salary and Commission or PT have different rates

I have a separate sheet with named ranges (Opt1, Opt2, Opt 3 and Den)
and
each one has the Dep status on the right column with the Sal, Comm/PT
or
GLR
heading across.

I think I could build this with a whole bunch of If..then.. statements
but
Select Case seems to be the way to go. I'm just having a hard time
wrapping
my head around it.

The last question is where do I put it so the rates pop up at the
right
time. Is this an OnExit procedure from a cell?

I usually use VLookup but I think this is too complicated for a
formula.
Any and all suggestions are welcome! If you can come up with a
formula,
please do!

Let me know if you need more info.

Thanks,
Alicia