Making a Formula Contingent on Check Boxes
 

I'm trying to make a formula that subtracts the correct surcharges
when checked off. For example:

=IF(G50,G5-SUM(F9,F10,F11,F12,F13,F14,F15,F18,F20,F21,F22))


I would like each of the F cells to only populate the formula when a
check box is checked off next to them. Does anyone know how I would
go about something like that? I believe it would have something to
do
with the Forms or ActiveX function. Any help would be greatly
appreciated. Thanks.

Making a Formula Contingent on Check Boxes
 

You don't say where you mean by
check box is checked off next to them
but assuming that you mean the cell immediately to the right (ie Column G)
then try:

=IF(G50,SUMPRODUCT(((G9:G15<"")*(F9:F15)))+((G18 <"")*(F18))+SUMPRODUCT(((G20:G22<"")*(F20:F22))) ,0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


wrote in message
oups.com...
I'm trying to make a formula that subtracts the correct surcharges
when checked off. For example:

=IF(G50,G5-SUM(F9,F10,F11,F12,F13,F14,F15,F18,F20,F21,F22))


I would like each of the F cells to only populate the formula when a
check box is checked off next to them. Does anyone know how I would
go about something like that? I believe it would have something to
do
with the Forms or ActiveX function. Any help would be greatly
appreciated. Thanks.

Making a Formula Contingent on Check Boxes
 

Ok, how much do you know about creating checkboxes?

Assuming column F contains numeric values and you create checkboxes in the
corresponding cells in column G. What you have to do is link each checkbox
to a cell. This linked cell will display the status of each checkbox, either
TRUE or FALSE, TRUE when the checkbox is checked and FALSE when the
checkbox is not checked.

Then you can get the sum of surcharges by summing those cells where the
corresponding linked cell = TRUE.

Your formula might look like this:

=IF(G50,G5-SUMIF(G9:G22,TRUE,F9:F22),"")

Post back if you need more assistance or if it'll help, I'll put together a
small sample file that demonstrates this.

--
Biff
Microsoft Excel MVP


wrote in message
oups.com...
I'm trying to make a formula that subtracts the correct surcharges
when checked off. For example:

=IF(G50,G5-SUM(F9,F10,F11,F12,F13,F14,F15,F18,F20,F21,F22))


I would like each of the F cells to only populate the formula when a
check box is checked off next to them. Does anyone know how I would
go about something like that? I believe it would have something to
do
with the Forms or ActiveX function. Any help would be greatly
appreciated. Thanks.

Making a Formula Contingent on Check Boxes
 

hi, i am facing similar problem and would really appreciate if you can post
an example here.

thanks,
Atiq

"T. Valko" wrote:

Ok, how much do you know about creating checkboxes?

Assuming column F contains numeric values and you create checkboxes in the
corresponding cells in column G. What you have to do is link each checkbox
to a cell. This linked cell will display the status of each checkbox, either
TRUE or FALSE, TRUE when the checkbox is checked and FALSE when the
checkbox is not checked.

Then you can get the sum of surcharges by summing those cells where the
corresponding linked cell = TRUE.

Your formula might look like this:

=IF(G50,G5-SUMIF(G9:G22,TRUE,F9:F22),"")

Post back if you need more assistance or if it'll help, I'll put together a
small sample file that demonstrates this.

--
Biff
Microsoft Excel MVP


wrote in message
oups.com...
I'm trying to make a formula that subtracts the correct surcharges
when checked off. For example:

=IF(G50,G5-SUM(F9,F10,F11,F12,F13,F14,F15,F18,F20,F21,F22))


I would like each of the F cells to only populate the formula when a
check box is checked off next to them. Does anyone know how I would
go about something like that? I believe it would have something to
do
with the Forms or ActiveX function. Any help would be greatly
appreciated. Thanks.

Making a Formula Contingent on Check Boxes
 

On Jul 2, 5:21 pm, "T. Valko" wrote:
Ok, how much do you know about creating checkboxes?

Assuming column F contains numeric values and you create checkboxes in the
corresponding cells in column G. What you have to do is link each checkbox
to a cell. This linked cell will display the status of each checkbox, either
TRUE or FALSE, TRUE when the checkbox is checked and FALSE when the
checkbox is not checked.

Then you can get the sum of surcharges by summing those cells where the
corresponding linked cell = TRUE.

Your formula might look like this:

=IF(G50,G5-SUMIF(G9:G22,TRUE,F9:F22),"")

Post back if you need more assistance or if it'll help, I'll put together a
small sample file that demonstrates this.

--
Biff
Microsoft Excel MVP



I don't know too much regarding checkboxes. I'm going to try to
figure it out today. I'm just a little unclear with respect to the
cell linking. I'll research the help and see what I come up with
although an example would help. Thanks!

Making a Formula Contingent on Check Boxes
 

On Jul 2, 5:21 pm, "T. Valko" wrote:
Ok, how much do you know about creating checkboxes?

Assuming column F contains numeric values and you create checkboxes in the
corresponding cells in column G. What you have to do is link each checkbox
to a cell. This linked cell will display the status of each checkbox, either
TRUE or FALSE, TRUE when the checkbox is checked and FALSE when the
checkbox is not checked.

Then you can get the sum of surcharges by summing those cells where the
corresponding linked cell = TRUE.

Your formula might look like this:

=IF(G50,G5-SUMIF(G9:G22,TRUE,F9:F22),"")

Post back if you need more assistance or if it'll help, I'll put together a
small sample file that demonstrates this.

--
Biff
Microsoft Excel MVP

<

Actually I figured it out. Thanks so much for your help with this!