checkbox for selecting options and summing

[jmoffett]

A few years ago, I received an Excel file from a co-worker. It was a departmental personnel listing with employee name and pay information. It was set up so I could either select or de-select a checkbox at the front of the row to see that employee's pay information reflected in the sum total at the bottom. How is this done? Thanks.

[Flintstone]

Hello Jmoffett:

From the menu bar click on View > Toolbars > Forms, the Forms dialog box will open then click on the Check Box icon and place where ever you want on your worksheet. When the check box is displayed on your sheet, right click on it and select Format Control to format the check box.


Matt

[jmoffett]

OK, I see that. But how do I make the sum total reflect only the lines that are checked?

[Flintstone]

The check box returns TRUE when checked and FALSE when not; where you assigned the cell to display this is in the Format Control, Control tab as “Cell linked”. As for the formula, that would depend on the situation of your worksheet.

Here’s a sample formula, assuming cell B2 is the linked cell and column A is the data you want to sum:

=IF(B2=TRUE,SUM(A2:A10),"")

I hope this helps you.

Matt

[jmoffett]

Great! That's just what I needed. One more question if I may...is there any way to insert check boxes into a cell so I can align them and keep their relative position to the row that they control? Thanks again.

[Flintstone]

Ya, I’m not to sure about that. I think the best way is to place your first check box just the way you want it, then right click and select copy, select your next cell, right click and select paste, and so on.

[jmoffett]

That's what I'll do. Thanks so much for your help.

[jmoffett]

I thought I knew how to do this, but I can't determine where to put the IF statement. Let's say I have a simple list of products. In column A, I have a check box; in column B I have a price; and in column C I have a quantity. In column D I want to multiply column B * column C if the check box in column A is checked.

Which cell returns true or false depending on whether the check box is checked? How do I determine this....what goes in the code for the check box? Where does my formula go [ =if(a1=TRUE,b1*c1,0) ]?

I appreciate your help and I'm sorry this is taking so long. Thanks again.

[Flintstone]

The formula you’ve shown will work fine, place it in whatever cell you want to see the results. Here’s a walk through to assign cell A1 as the linked cell:

1. Right click on the check box and select Format Control, the Format Control dialog box opens.
2. At the top you will notice 6 tabs with which you can format the check box, select the Control tab.
3. Where it says “Cell link:”, type in the reference A1, now click the OK button.

Matt

[jmoffett]

Thanks for all your help. The checkboxes are working perfectly. If you don't mind, I would like to expand my options a little. How would I format a radio-type option button so that only one option could be selected?

For instance, let's say I have two products on two separate lines. I want to select one or the other, but not be able to select both at the same time. What would I have to do to format the buttons to do this?

Thanks again.

[jmoffett]

Thanks for all your help. The checkboxes are working perfectly. If you don't mind, I would like to expand my options a little. How would I format a radio-type option button so that only one option could be selected?

For instance, let's say I have two products on two separate lines. I want to select one or the other, but not be able to select both at the same time. What would I have to do to format the buttons to do this?

Thanks again.