Check box = 1

[Dave]

I'm pretty out of my depth here, so any help would be much appreciated.

I'm trying to set up an attendance form to cover a 24 hour perid. i'd like a
columnof 14 checkboxes (places available per hour), and at the bottom a total
of how many boxes have been checked, and then repeated for the following 23
columns.

How do i go about giving a cell the value of "1"if the box is checked, and
accumulated the total of checked boxes. I'm struggling to get past changing
"true"/"false" values to "1"/"0" values.

Also, do i have to do this 2352 seperate times (14 places x 24 hours x 7
days a week), or can i do copy and paste?

 

[Arceedee]

I hope I understand what you are doing.
How about making the "check" the number 1 and then apply conditional
formatting to colour the cell and text the same colour. Counting that would
be easy. There are other "count" formulae which would do the job but I have
been using a similar method and it also shows graphically how things are
progressing.

You should only need to do one cell and then copy as much as you need.

 

[Dave Peterson]

First, I'd make my life simple(r) and use X's in cells instead of checkboxes.

Then I could use a formula like:
=countif(a1:a14,"x")

But if you wanted, you could assign a linked cell to each checkbox.

Then you could use
=countif(a1:a14,true)

I'd use the cell that contains the checkbox, but hide the value in that cell by
using a custom format of:

;;;
(3 semicolons will hide the value in the cell, but it'll still show up in the
formulabar)

Assigning the linked cell will depend on what kind of checkbox you used.

If you used checkboxes from the Forms toolbar, then...
right click on each
Choose Format Control|Control tab
and type in the address (or point at the cell)

If you used checkboxes from the Control toolbox, then...
right click on each
Choose Properties
Scroll down to LinkedCell
and type in the address

================================
(saved from a previous post)

How about another alternative...

Select the range that would have held the checkboxes.
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings (make it as large as you want)

Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:
=if(a1="","no checkmark","Yes checkmark")
or
=counta(a1:a10)
to get the number of "checked" cells in A1:A10

Or you can filter by blanks and non-blanks.