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

<[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>>
>
>

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

<[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>>> 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?
>>>
>>>
>>>
>>
>>
>
>

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?

Ahh I see, it multiplies by 1 or zero depending on state. Genius! Thanks a
bunch!

"Bernard Liengme" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>> <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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?
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>