Check boxes linked to other cells?

Is it possible to have the checkbox value (either checked or unchecked)
dependent on if the number in another cell is positive or negative (greater
than or less than zero)?

I've tried the "cell link" option on the "control" tab of the format control
popup menu, but it doesn't return what I want. It seems to do the opposite.
It takes the value of the checkbox and returns a true or false in the linked
cell. Is there a way to reverse it?

I'm using this on an expense voucher form and the checkbox indicates if the
final balance due (after deducting any advanced monies) is payable to the
company (negative balance) or payable to the employee (positive balance).

Any help is appreciated. Using Excel 2002 with Windows XP Professional.

Hi,

Am not sure if this is possible with Checkbox created using Forms
toolbar, but i have got a workaround.

Enter the following simple formula in the cell (Say B1) , where you want
the Checkbox :

=IF(A1>0,"a","") where A1 is the cell which has got the positive or
negative number.

And then format the cell B1 to the Font called 'Marlett'.

Now, when A1 is more than 0, B1 will return 'a'. The alphabet 'a' is
shown as a checkbox in the Marlett Font and hence you would see a
checkbox. If A1 is less than 0, the formula returns a blank and it would
appear as a blank in Marlett Font also.

Regards

Govind.


C. Cunningham wrote:

> Is it possible to have the checkbox value (either checked or unchecked)
> dependent on if the number in another cell is positive or negative (greater
> than or less than zero)?
>
> I've tried the "cell link" option on the "control" tab of the format control
> popup menu, but it doesn't return what I want. It seems to do the opposite.
> It takes the value of the checkbox and returns a true or false in the linked
> cell. Is there a way to reverse it?
>
> I'm using this on an expense voucher form and the checkbox indicates if the
> final balance due (after deducting any advanced monies) is payable to the
> company (negative balance) or payable to the employee (positive balance).
>
> Any help is appreciated. Using Excel 2002 with Windows XP Professional.

Hi,

Thanks for the workaround, but the forms will be placed on the company wide
intranet, and not everyone who will access the form will have the same fonts.
Also, I need there to be a blank checkbox if a checked box does not apply.

Does anyone else have any suggestions?



"Govind" wrote:

> Hi,
>
> Am not sure if this is possible with Checkbox created using Forms
> toolbar, but i have got a workaround.
>
> Enter the following simple formula in the cell (Say B1) , where you want
> the Checkbox :
>
> =IF(A1>0,"a","") where A1 is the cell which has got the positive or
> negative number.
>
> And then format the cell B1 to the Font called 'Marlett'.
>
> Now, when A1 is more than 0, B1 will return 'a'. The alphabet 'a' is
> shown as a checkbox in the Marlett Font and hence you would see a
> checkbox. If A1 is less than 0, the formula returns a blank and it would
> appear as a blank in Marlett Font also.
>
> Regards
>
> Govind.
>
>
> C. Cunningham wrote:
>
> > Is it possible to have the checkbox value (either checked or unchecked)
> > dependent on if the number in another cell is positive or negative (greater
> > than or less than zero)?
> >
> > I've tried the "cell link" option on the "control" tab of the format control
> > popup menu, but it doesn't return what I want. It seems to do the opposite.
> > It takes the value of the checkbox and returns a true or false in the linked
> > cell. Is there a way to reverse it?
> >
> > I'm using this on an expense voucher form and the checkbox indicates if the
> > final balance due (after deducting any advanced monies) is payable to the
> > company (negative balance) or payable to the employee (positive balance).
> >
> > Any help is appreciated. Using Excel 2002 with Windows XP Professional.
>

Thanks Govind, I was actually able to use your formula in different way, but
now it returns exactly what I want it to do.

I did a little bit more reseach on the web on the cell link option, and
found the following tips website:
http://exceltips.vitalnews.com/Pages...eck_Boxes.html

In this page it talks about the fact that "When the check box changes, the
contents of this cell change; when the contents of the cell are changed, the
check box reflects that change--it is a bi-directional relationship."

So, first I linked the check box to the cell underneath it. Then I went
into that cell and entered the formula =IF(L31>0,TRUE,FALSE). For the other
checkbox, I just reversed the < sign in the formula. Then depending on
whether the formula in L31 returns a negative or positive, the checkboxes
behave accordingly. Lastly, I went in and changed the text color of my
linked cells to white, so that the formula would be hidden, and then I locked
the cells.

Thanks for pointing me in the right direction!

"C. Cunningham" wrote:

> Hi,
>
> Thanks for the workaround, but the forms will be placed on the company wide
> intranet, and not everyone who will access the form will have the same fonts.
> Also, I need there to be a blank checkbox if a checked box does not apply.
>
> Does anyone else have any suggestions?
>
>
>
> "Govind" wrote:
>
> > Hi,
> >
> > Am not sure if this is possible with Checkbox created using Forms
> > toolbar, but i have got a workaround.
> >
> > Enter the following simple formula in the cell (Say B1) , where you want
> > the Checkbox :
> >
> > =IF(A1>0,"a","") where A1 is the cell which has got the positive or
> > negative number.
> >
> > And then format the cell B1 to the Font called 'Marlett'.
> >
> > Now, when A1 is more than 0, B1 will return 'a'. The alphabet 'a' is
> > shown as a checkbox in the Marlett Font and hence you would see a
> > checkbox. If A1 is less than 0, the formula returns a blank and it would
> > appear as a blank in Marlett Font also.
> >
> > Regards
> >
> > Govind.
> >
> >
> > C. Cunningham wrote:
> >
> > > Is it possible to have the checkbox value (either checked or unchecked)
> > > dependent on if the number in another cell is positive or negative (greater
> > > than or less than zero)?
> > >
> > > I've tried the "cell link" option on the "control" tab of the format control
> > > popup menu, but it doesn't return what I want. It seems to do the opposite.
> > > It takes the value of the checkbox and returns a true or false in the linked
> > > cell. Is there a way to reverse it?
> > >
> > > I'm using this on an expense voucher form and the checkbox indicates if the
> > > final balance due (after deducting any advanced monies) is payable to the
> > > company (negative balance) or payable to the employee (positive balance).
> > >
> > > Any help is appreciated. Using Excel 2002 with Windows XP Professional.
> >

Hi,

Great to know of that. I wasnt aware of the bi-directional relationship
between Cell link and the checkbox. Thanks for letting me know on that.

Regards

Govind.

C. Cunningham wrote:
> Thanks Govind, I was actually able to use your formula in different way, but
> now it returns exactly what I want it to do.
>
> I did a little bit more reseach on the web on the cell link option, and
> found the following tips website:
> http://exceltips.vitalnews.com/Pages...eck_Boxes.html
>
> In this page it talks about the fact that "When the check box changes, the
> contents of this cell change; when the contents of the cell are changed, the
> check box reflects that change--it is a bi-directional relationship."
>
> So, first I linked the check box to the cell underneath it. Then I went
> into that cell and entered the formula =IF(L31>0,TRUE,FALSE). For the other
> checkbox, I just reversed the < sign in the formula. Then depending on
> whether the formula in L31 returns a negative or positive, the checkboxes
> behave accordingly. Lastly, I went in and changed the text color of my
> linked cells to white, so that the formula would be hidden, and then I locked
> the cells.
>
> Thanks for pointing me in the right direction!
>
> "C. Cunningham" wrote:
>
>
>>Hi,
>>
>>Thanks for the workaround, but the forms will be placed on the company wide
>>intranet, and not everyone who will access the form will have the same fonts.
>> Also, I need there to be a blank checkbox if a checked box does not apply.
>>
>>Does anyone else have any suggestions?
>>
>>
>>
>>"Govind" wrote:
>>
>>
>>>Hi,
>>>
>>>Am not sure if this is possible with Checkbox created using Forms
>>>toolbar, but i have got a workaround.
>>>
>>>Enter the following simple formula in the cell (Say B1) , where you want
>>>the Checkbox :
>>>
>>>=IF(A1>0,"a","") where A1 is the cell which has got the positive or
>>>negative number.
>>>
>>>And then format the cell B1 to the Font called 'Marlett'.
>>>
>>>Now, when A1 is more than 0, B1 will return 'a'. The alphabet 'a' is
>>>shown as a checkbox in the Marlett Font and hence you would see a
>>>checkbox. If A1 is less than 0, the formula returns a blank and it would
>>>appear as a blank in Marlett Font also.
>>>
>>>Regards
>>>
>>>Govind.
>>>
>>>
>>>C. Cunningham wrote:
>>>
>>>
>>>>Is it possible to have the checkbox value (either checked or unchecked)
>>>>dependent on if the number in another cell is positive or negative (greater
>>>>than or less than zero)?
>>>>
>>>>I've tried the "cell link" option on the "control" tab of the format control
>>>>popup menu, but it doesn't return what I want. It seems to do the opposite.
>>>>It takes the value of the checkbox and returns a true or false in the linked
>>>>cell. Is there a way to reverse it?
>>>>
>>>>I'm using this on an expense voucher form and the checkbox indicates if the
>>>>final balance due (after deducting any advanced monies) is payable to the
>>>>company (negative balance) or payable to the employee (positive balance).
>>>>
>>>>Any help is appreciated. Using Excel 2002 with Windows XP Professional.
>>>