checkbox for formula B. 12/30/2008 3:18:03 PM 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.

Re: checkbox for formula "David Biddulph" <groups [at] biddulph.org.uk> 12/30/2008 3:34:29 PM 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." <B.[ at ]discussions.microsoft.com> wrote in message news:6ED566BD-46DC-4BFA-BCAB-8E477658D915[ at ]microsoft.com... [Quoted Text] > 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. >

Re: checkbox for formula "Bernard Liengme" <bliengme[ at ]stfx.TRUENORTH.ca> 12/30/2008 3:41:01 PM 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." <B.[ at ]discussions.microsoft.com> wrote in message news:6ED566BD-46DC-4BFA-BCAB-8E477658D915[ at ]microsoft.com... [Quoted Text] > 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. >

Re: checkbox for formula B. 12/31/2008 12:57:21 PM 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: [Quoted Text] > 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." <B.[ at ]discussions.microsoft.com> wrote in message > news:6ED566BD-46DC-4BFA-BCAB-8E477658D915[ at ]microsoft.com... > > 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. > > > > >

Re: checkbox for formula "Bernard Liengme" <bliengme[ at ]stfx.TRUENORTH.ca> 12/31/2008 5:51:06 PM 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." <B[ at ]discussions.microsoft.com> wrote in message news:C0E1351C-E79D-4CC6-9EC5-B13830405FCC[ at ]microsoft.com... [Quoted Text] > 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." <B.[ at ]discussions.microsoft.com> wrote in message >> news:6ED566BD-46DC-4BFA-BCAB-8E477658D915[ at ]microsoft.com... >> > 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. >> > >> >> >>