checkbox for formula

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.
 
D

David Biddulph

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,"")
 
B

Bernard Liengme

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
 
B

B.

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.
 
B

Bernard Liengme

I do not follow your problem. Can you send me a file? Just remove TRUENORTH.
for email address.
best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top