How can I set up a series of "check boxes" in a column whose totals can be counted?

Posted by Chris Rock on September 29, 2001 4:39 AM

I've got a form I am trying to create for other users. I've got data that can be categorized one of six different ways, so I've set up 6 columns. The user will mark which category the data belongs in.

I'd like to use check boxes. My problem is, there are A LOT of rows. Thousands. So what I'd like is an easy way to set up several thousand check boxes.

When you set up a check box, I know you create a cell link to the check box. It becomes TRUE if it's checked, and FALSE if it's unchecked. I need a way to "fill down" the check boxes, so that the cell link is changed for each new check box.

When I am done, I'll simply count the TRUE's and FALSE's and have my totals. I'll also set up some kind of data validation, so I can't have 2 TRUES in the same row. But what I really need help with is creating the different checkboxes and their cell links themselves. Is there an easier way (using some kind of fill command) than manually changing each cell reference within the Format Control menu?

Thanks in advance.

Posted by David Holstein on September 29, 2001 7:38 AM

Re: How can I set up a series of

===============================================
Hi Chris,

There probably is a way to do this if you can program in VB Editor but it is probably not worthwhile and the file will be enormous and may (probably) become corrupt at various stages, especially on a network.

If what I understand, 1st Column is the DATA that you would like evaluted going down the page.
In the 2nd Column you can use DATA VALIDATION to put the response in just one column.

1) Select Row 1 insert about 7-8 rows above this.
2) In A1 - A6 type in your 6 responses.
3) Highlight the 6 values and select INSERT => NAME => DEFINE = type in a name (eg: ResponseRange) and click OK
4) Highlight the entire 2nd column and select DATA => VALIDATION => select LIST in the 'Allow' box

Click on the ARROW in the right of the SOURCE box, browse and highlight A1 - A6 => OK

In DATA VALIDATION you can also add ENTRIES messages and WRONG VALUE messages to prompt the user.

What you will now find in 2nd Column is drop down boxes with your 6 values for the user to select from.

5) Hide the rows about your HEADINGS highlight them and select HIDE
6) If you ever want to add more then 6 values you will need to unhide them, highlight the row that contains the 6th values and select INSERT ROW and insert the new value in the new row.

7) To analysis data, on another sheet, you could have a table with 6 values going down page
and a COUNTIF formula to the right of it (eg: COUNTIF('Sheet1!B:B,"Value 1"), changing VALUE 1 where relevant.

Consider at this stage instead of having RESPONSE as the name for 2nd Column you could name it with a USERS NAME and then copy the ENTIRE 2nd Column to Column 3,4,5,6 etc and rename for all users for easy analysis.

Hope this helps as what you want to do is difficult.