Linking Cells with Check Boxes

See Attachment.

On the complaints table tab row 9 I have set up a formula to count the amount of complaints received in each month. That row will be hidden with row 8 picking up what is being shown in row 9.

For the months where there are no complaints, i want to be able to tick the check boxes, and once they have been ticked a zero will appear in the blank cells in row 8.

Thank you for any help you are able to give me. Cheers!!

Dandavis,

If you use a Check Box, you'd better evaluate the state of the Check Box. That does not happen automatically.

Use the Cell link in the Format Control of the Check Box to link the state of the Check Box to a cell. For example, link the Check Box that sits in D6 to cell D6. When the Check Box is ticked, cell D6 will have the value of TRUE, if it is unticked, it will have the value of FALSE.

The value will show in the cell, so you may want to format the cell with white text on white background or with custom format ;;; to suppress the display of the value.

Then, in row 8 use a formula like

=IF(D6,"",IF(D9="",0,D9))

If D6 is TRUE (i.e. if the box is checked, then D8 will show "", otherwise the second IF statement will be evaluated.

Above applied to column D in attached file.

In the Complaints table, change the formula in columns D to O from

=IF(E8,"0",IF(E11="",0,E11))

to

=IF(E8,0,IF(E11="",0,E11))

Now the cell value will be a number, not text, and the conditional formatting in the 2nd generation dependents will pick that up correctly.

cheers