Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check boxes linked to other cells?
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(A10,"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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check boxes linked to other cells?
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(A10,"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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check boxes linked to other cells?
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(L310,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(A10,"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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check boxes linked to other cells?
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(L310,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(A10,"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Boxes & Data Validation | Excel Discussion (Misc queries) | |||
Preventing multiple check boxes from being checked | Excel Discussion (Misc queries) | |||
requires that merged cells must be identically sized? | Excel Discussion (Misc queries) | |||
Linked cells | Excel Discussion (Misc queries) | |||
Adding Check Boxes | Excel Discussion (Misc queries) |