Check box and and formula

  • Thread starter Russell-stanely
  • Start date
R

Russell-stanely

Hi. I am trying to desing a form where the user checks one box at the top of
the form and then at the bottom of the form there is a printed message based
on what check box was checkced at the top. The form has about 60 check boxes
at the top, and I want the printed message to print in only one cell, based
on which check box was checked at the top. (I hope this is clear!) I do
not know macros or vb and was hoping a formula (such as If(or) wouldr
work.\\\\\\\\\
 
B

Biff

Hi!

That's a lot of checkboxes on a form!
I do not know macros or vb
Click to expand...

Well, neither do I so I'm assuming you used the Forms toolbar to create the
checkboxes.

Here's an idea....

Link each checkbox to a cell. eg:

link ckbx1 to cell J1, ckbx2 to J2, ckbx3 to J3, ckbx4 to
J4............ckbx60 to J60.

Then in column K, K1:K60 list the message associated with each checkbox.

Formula to return the message depending on which checkbox is selected:

=IF(ISNA(VLOOKUP(TRUE,J1:K60,2,0)),"",VLOOKUP(TRUE,J1:K60,2,0))

Biff
 
R

Russell-stanely

Biff---thanks for your help. I am very close to what I want to accomplish,
but I have a question about the formula example you gave me.

In your example, the cells are the same at the front of the formula and the
end of the formula. (J1:K60) Shouldn't I plug in the NEXT set of cells in
the second half of the formula so that the message is returned accoridng to
the cell that is checked?

EX: I have check box 1 linked to cell b1 with the message in c1
I have check box 2 linked to cell b2, with the message in c2, and so on.

Should the formula read,
=IF(ISNA(VLOOKUP(TRUE, B1:C1,2,0)),"",VLOOKUP(TRUE, B2:C2,2,0)). Would
this return the message linked to checkbox 2 if checkbox 2 was the only box
checked? I tried it this way, and I got a #N/A message.
Again, thanks for your help!!
 
R

Russell-stanely

Biff--A little more information: I did not clarify that only one box at a
time is going to be checked. So the formula needs to take into account that
either checkbox #1 is going to be checked and the message for this box needs
to be displayed, OR only checkbox #2 is going to be checked, so the message
for this box needs to be displayed, OR only checkbox #3 is going to be
checked, etc etc etc etc.
 
B

Biff

Hi!

Ok, I didn't fully understand what you wanted. I read this:
I want the printed message to print in only one cell
Click to expand...

....to mean that no matter what checkbox was selected the message would
appear in the same cell.

If you have the checkboxes linked to column B then something like this will
work:

=IF(B1=TRUE,"your_message_here","")

Then you can just drag copy down the column.

Biff
 
R

Russell-stanely

Biff--thanks for the answer. But I am not sure that I understand it.

There are 60 checkboxes on my form. Each checkbox has their own specific
instructions. I want the person using the form to be able to check one box,
and then the specifc instructions for that checked box, to print on the
bottom of the form. Only one box will be checked at a time.
I have all of the checkboxes linked to a cell as you suggested, and I have
all of the specific instructions in the cell next to it.
The problem I am having is trying to figure out how to write the formula on
the cell where the instructions are going to print. Because it seems that
the formula would have to be written as an OR formula... If Check box 1
=true, then "your message here", OR if check box 2=true, then "your message
here", or if check box 3=true, then "your message here",..etc etc . I
haven't figured out how to write this formula.
Any suggestions?
thanks for your help!
 
B

Biff

Hi!

OK, let's try this....

Tell me if I have this right.

Assume cells A1:A60 have checkboxes.
Those checkboxes are linked to cells B1:B60.
The message for each checkbox is in the range C1:C60

When a checkbox is checked, you want the message that corresponds to that
checkbox from column C to appear in a cell at the bottom of the form?

If that's what you want then the vlookup suggestion I made will work. If
that's not what you want, can you send me the file to have a look? If you
can send me the file, I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Or, I can put together a small sample file and demonstrate a couple of
interpretations of what it is *I* think you're wanting to do.

Either way, let me know.

Biff
 
R

Russell-stanely

THANK YOU SO MUCH!
I am sending you the s/s. you can see on sheet 1 under the area titled,
PLUGS and TORQUE..these is where I want the instructions to print.
Insctructions are on sheet 3.

thanks again
 
Top