Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have Checkbox A uncheck with checked Checkbox B | Excel Discussion (Misc queries) | |||
i want to base a formula off whether a checkbox is filled in | Excel Discussion (Misc queries) | |||
Excel Checkbox can be regconized in a formula and be executed | Excel Worksheet Functions | |||
check a checkbox in a formula | Excel Worksheet Functions | |||
checkbox | Excel Worksheet Functions |