Can a Check Box swap Formulas

**rbpd5015** · 06-30-2010, 03:45 PM

Guys,

I have two formulas

=IFERROR(IF(H18="","",CEILING(IF(H18<0,H18*(($C$17*'MLB OVERALL'!$E$7)+$C$16)/-100,(($C$17*'MLB OVERALL'!$E$7)+$C$16)/$H18*100),1)), "")

and

=IFERROR(IF(H18="","",CEILING(IF(H18<0,H18*(((G18*-1)*'MLB OVERALL'!$E$7)+$C$16)/-100,(((G18*-1)*'MLB OVERALL'!$E$7)+$C$16)/$H18*100),1)), "")

these two formulas are in cells
J18:J25 and on sheets
Week 1a:week 26b

what I want is to create a check box on my main sheet

which if checked does formula #2 and if unchecked does formula #1?

Is this possible and how please?

Matt

**ron2k_1** · 06-30-2010, 03:52 PM

Create the check box, right click on the box and click on "Format Control" and on Cell link, link it to say A1.

Then on J18:J25

=IF($A$1="True",IFERROR(IF(H18="","",CEILING(IF(H18<0,H18*(($C$17*'MLB OVERALL'!$E$7)+$C$16)/-100,(($C$17*'MLB OVERALL'!$E$7)+$C$16)/$H18*100),1)), ""),IFERROR(IF(H18="","",CEILING(IF(H18<0,H18*(((G18*-1)*'MLB OVERALL'!$E$7)+$C$16)/-100,(((G18*-1)*'MLB OVERALL'!$E$7)+$C$16)/$H18*100),1)), ""))

Try it and let us know.

1st EDIT: Change "A1" to whatever cell you link the box to. This could be on another sheet
2nd EDIT: My bad I confused the order of how you wanted it. If checked you want IF number 2. just change the word "TRUE" to "False"

**rbpd5015** · 06-30-2010, 04:55 PM

Ok,

This is what I got and now matter what happens I am getting

this part of the formula

=IFERROR(IF(H18="","",CEILING(IF(H18<0,H18*(((G18*-1)*'MLB OVERALL'!$E$7)+$C$16)/-100,(((G18*-1)*'MLB OVERALL'!$E$7)+$C$16)/$H18*100),1)), "")

The Formula

=IF('MLB OVERALL'!$C$9="True",IFERROR(IF(H21="","",CEILING(IF(H21<0,H21*(($C$17*'MLB OVERALL'!$E$7)+$C$16)/-100,(($C$17*'MLB OVERALL'!$E$7)+$C$16)/$H21*100),1)), ""),IFERROR(IF(H21="","",CEILING(IF(H21<0,H21*(((G21*-1)*'MLB OVERALL'!$E$7)+$C$16)/-100,(((G21*-1)*'MLB OVERALL'!$E$7)+$C$16)/$H21*100),1)), ""))

**shg** · 06-30-2010, 05:20 PM

I have no idea what that last formula is supposed to do, but believe it simplifies to

=IFERROR(IF(H21="", "", CEILING(IF(H21<0, H21*(IF(M!$C$9, $C$17, -G21)*M!$E$7 - $C$16)/100, (IF(M!$C$9, $C$17, -G21)*M!$E$7+$C$16)/$H21*100), 1)), "")