checkbox for formula

  • Thread starter Thread starter B.
  • Start date Start date
B

B.

Hi Folks,

I have a worksheet with basic formulas for addition and subtraction. For
example; when I put the number on G7 it subtracts from H6 and shows the
result on H7. I am using this formula on H7
=IF(AND(ISBLANK(F7),ISBLANK(G7)),"",H6-F7+G7)

Now I would like to place a checkbox or radio button on E7 to control
calculation on H7. If it is checked the calculation will be happened if not
there will be no calculation. I am using Excel 2007.

I spent my last 5 hours for this but no avail. Any help will be appreciated.
Thanks,
B.
 
Does your Ribbon have a Developer tab?
If not: Office button / Excel Options / Popular (on left side of dialog),
then check Show Developer on Ribbon (third item down)

Click Developer tab, locate the Controls group (second from left), click the
Insert icon
Click the Check box (third item on top row of Forms Control set) - do not
use the Active X control
Use the mouse to set the required position
Right click the Check Box, use Format Control; open the Control tab and link
the Control box to cell E7
Clicking the Control Box cycles E7 thru FALSE/TRUE
Edit formula in H7 to read:
=IF(AND(ISBLANK(F7),ISBLANK(G7)),"",IF(E7,H6-F7+G7,""))
You may wish to format the control box to make it opaque and hide the value
in E7
best wishes
 
Thank you very much for your answers. It solved my problem but also caused
another one. Since the formula refers to previous cell if it is not checked
the cell will be emty. For example; formula controls H7. If it is not checked
H7 will be emty. Thus, the next cell calculation on H8 gives #VALUE error.

Is there any way to change the formula
=IF(AND(ISBLANK(F7),ISBLANK(G7)),"",IF(E7,H6-F7+G7,"")) to one that
calculates from last non emty cell in the column?

Grateful to you for your answers,
B.
 
I do not follow your problem. Can you send me a file? Just remove TRUENORTH.
for email address.
best wishes
 
Back
Top