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?

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?

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?

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?

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?

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?