use "button" to make calculation ignore a cell

G

Guest

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

Bernard Liengme

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
 
G

Guest

Thanks for the quick reply
How do I link it? I can assign macros of course but not "Link"
I'm using XL2003
 
B

Bernard Liengme

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
 
G

Guest

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?
 
Top