Deactivate cells based on checkbox selection and exclude inactive cells from SUM

[olga6542]

I was wondering if you could help me with the following:
If I select attribute B, I want cells B10, C10, B13 and C13 deactivated. Once deactivated it, the sum should be updated to calculate only active cells. So, if I select attribute B, the total sum should be updated to 6,500.

[6StringJazzer]

What do you mean by "deactivated" and "active" in this case?

This update does exactly what you describe but of course you didn't mention what do to if A or C is checked.

The easiest way to do this is with formulas. I linked each of your checkboxes to a cell, named those cells, and then the SUM formulas checks those cells to determine which values to include.

If you select attribute B, and then B10, C10, B13, and C13 are deactivated, and you want to sum only the active cells, the correct answer is 5,000, not 6,500.

[olga6542]

No, it doesn't work. If checkbox B is selected, option 1 and option 2 have to be deactivated, as it is not applicable. So, it has to sum to 10,000 is option B is not selected. If option B is selected, it has to be deactivated (grey-out but not locked), so the sum is updated and the sum only calculates active cells. So, option 1 and option 2 are not applicable when Option B is selected, the sum has to be $6,500.

[6StringJazzer]

How are you getting 6,500?

[6StringJazzer]

Here is a revision but your arithmetic seems to be wrong.

This version revises it to give the total of all numbers if B is not checked. It uses conditional formatting to gray out the "deactivated" cells (these cells are in no way deactivated except conceptually).

[olga6542]

I didn't write the right cells. If I deactivate Option 2 and Option 4, I would get $6,500. Your solution works. Can you walk me through exactly how you did it? Did you have to write any code? How did you define the name with the checkbox? Can you do it without name manager?

Thanks.

[6StringJazzer]

Here is what I did. No VBA code was used.

I linked each of your checkboxes to a cell

Go to the Developer panel and click Design. Right-click the checkbox and select Properties. Type the address of the desired cell into the LinkedCell property. When the box is checked, this cell will be True and when the box is unchecked it will be False.

named those cells

This step is unnecessary but allows the formulas be more readable. You must use Name Manager to do this. If you prefer you can just use the cell addresses in the formula.

and then the SUM formulas checks those cells to determine which values to include.

For each cell, I specified the conditions that determine whether that cell is included in the sum.

Please Login or Register  to view this content.

Based on your last post it would be corrected to

Please Login or Register  to view this content.

I also used Conditional Formatting to set the cells to be grayed out when checkbox B is selected. This is just for appearances.