checkbox for formula
 

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.

checkbox for formula
 

If E7 is returning TRUE or FALSE, you could change your formula to
=IF(E7,IF(AND(ISBLANK(F7),ISBLANK(G7)),"",H6-F7+G7),"")
or
=IF(AND(E7,COUNT(F7:G7)),H6-F7+G7,"")
--
David Biddulph

"B." wrote in message
...
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.

checkbox for formula
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"B." wrote in message
...
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.

checkbox for formula
 

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.

checkbox for formula
 

I do not follow your problem. Can you send me a file? Just remove TRUENORTH.
for email address.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"B." wrote in message
...
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.


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"B." wrote in message
...
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.