|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 20th, 2006, 07:51 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use "button" to make calculation ignore a cell
How can I create a macro "button" over a series of columns that will cause a
formula in another part of the sheet to ignore the value that the column calculates to? i.e. X X X 20 60 9 40 50 21 _______________ 60 110 30 =200 I would like to click on the button (represented by the X) or perhaps more than one button, have the button change appearance and then that column total wouldn't be used to calculate the answer (200) Does that make since? 
|
|
#2
March 20th, 2006, 08:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use "button" to make calculation ignore a cell
Use View | Toolbars to display Forms tool
Make a check box on the worksheet; link it to a cell The check box gives a values of TRUE (1) or FALSE (0) to a cell I linked three check boxes to A3, B3, and C3 I had three numbers in the rows below each I used formula =SUM(A4:A6)*A3+SUM(B4:B6)*B3+SUM(C4:C6)*C3 You can format and move the checkbox to hid the linked cell best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... How can I create a macro "button" over a series of columns that will cause a formula in another part of the sheet to ignore the value that the column calculates to? i.e. X X X 20 60 9 40 50 21 _______________ 60 110 30 =200 I would like to click on the button (represented by the X) or perhaps more than one button, have the button change appearance and then that column total wouldn't be used to calculate the answer (200) Does that make since?
|
|
#3
March 20th, 2006, 08:29 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use "button" to make calculation ignore a cell
Thanks for the quick reply
How do I link it? I can assign macros of course but not "Link" I'm using XL2003 "Bernard Liengme" wrote in message ... Use View | Toolbars to display Forms tool Make a check box on the worksheet; link it to a cell The check box gives a values of TRUE (1) or FALSE (0) to a cell I linked three check boxes to A3, B3, and C3 I had three numbers in the rows below each I used formula =SUM(A4:A6)*A3+SUM(B4:B6)*B3+SUM(C4:C6)*C3 You can format and move the checkbox to hid the linked cell best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... How can I create a macro "button" over a series of columns that will cause a formula in another part of the sheet to ignore the value that the column calculates to? i.e. X X X 20 60 9 40 50 21 _______________ 60 110 30 =200 I would like to click on the button (represented by the X) or perhaps more than one button, have the button change appearance and then that column total wouldn't be used to calculate the answer (200) Does that make since?
|
|
#4
March 20th, 2006, 08:34 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use "button" to make calculation ignore a cell
Right click CheckBox
Use Format Control from menu Open Control tab and locate Cell Link box I find it best to click on the cell with the dialog open rather than type cell reference best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... Thanks for the quick reply How do I link it? I can assign macros of course but not "Link" I'm using XL2003 "Bernard Liengme" wrote in message ... Use View | Toolbars to display Forms tool Make a check box on the worksheet; link it to a cell The check box gives a values of TRUE (1) or FALSE (0) to a cell I linked three check boxes to A3, B3, and C3 I had three numbers in the rows below each I used formula =SUM(A4:A6)*A3+SUM(B4:B6)*B3+SUM(C4:C6)*C3 You can format and move the checkbox to hid the linked cell best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... How can I create a macro "button" over a series of columns that will cause a formula in another part of the sheet to ignore the value that the column calculates to? i.e. X X X 20 60 9 40 50 21 _______________ 60 110 30 =200 I would like to click on the button (represented by the X) or perhaps more than one button, have the button change appearance and then that column total wouldn't be used to calculate the answer (200) Does that make since?
|
|
#5
March 20th, 2006, 08:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use "button" to make calculation ignore a cell
OK got that,
in your formula: =SUM(A4:A6)*A3+SUM(B4:B6)*B3+SUM(C4:C6)*C3 does the "*A3" tell the formula to evaluate if the link in A3 is true? Is that how it works?
|
|
#6
March 21st, 2006, 04:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use "button" to make calculation ignore a cell
Ahh I see, it multiplies by 1 or zero depending on state. Genius! Thanks a
bunch! "Bernard Liengme" wrote in message ... Right click CheckBox Use Format Control from menu Open Control tab and locate Cell Link box I find it best to click on the cell with the dialog open rather than type cell reference best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... Thanks for the quick reply How do I link it? I can assign macros of course but not "Link" I'm using XL2003 "Bernard Liengme" wrote in message ... Use View | Toolbars to display Forms tool Make a check box on the worksheet; link it to a cell The check box gives a values of TRUE (1) or FALSE (0) to a cell I linked three check boxes to A3, B3, and C3 I had three numbers in the rows below each I used formula =SUM(A4:A6)*A3+SUM(B4:B6)*B3+SUM(C4:C6)*C3 You can format and move the checkbox to hid the linked cell best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... How can I create a macro "button" over a series of columns that will cause a formula in another part of the sheet to ignore the value that the column calculates to? i.e. X X X 20 60 9 40 50 21 _______________ 60 110 30 =200 I would like to click on the button (represented by the X) or perhaps more than one button, have the button change appearance and then that column total wouldn't be used to calculate the answer (200) Does that make since?
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Can I make a formula in Excel to display result in same cell? | Neiko | Worksheet Functions | 2 | October 1st, 2005 10:36 PM |
| copying cell names | Al | General Discussion | 3 | August 11th, 2005 03:01 PM |
| cell color index comparison | MINAL ZUNKE | New Users | 1 | June 30th, 2005 07:11 AM |
| how do I make a word typed in a cell go to a specific cell in anot | Lmatarazzo | General Discussion | 3 | April 21st, 2005 04:29 AM |
| Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |
Linear Mode
