Hi
I have a Google Spreadsheet on which I want to use the IF formula on Checkboxes. What I'm trying to achieve is that if the box is checked (TRUE), the formula should give "Paid" and if it is unchecked (FALSE), the formula should give "Unpaid"
Since it's on Google Sheets I can't share the file itself but I have attached a screenshot. I used the formula =IF(E9=TRUE,"Paid","Unpaid") but this ends up giving an error each time.
are those boxes actually PART of cells? I wouldn't think so. how did you add them? I don't use goog shts. according to both of these, it seems like the issue is that cell e9 is NOT a box, and there is no pointer associated, which makes perfect sense:
https://support.google.com/docs/answ...DDesktop&hl=en
https://support.google.com/a/users/answer/9308622?hl=en
looks like the code is right tho:
https://support.google.com/docs/answer/3093364?hl=en
One would think the checkbox would have to be linked to E9
Originally Posted by vba_php
I've tried all these references but they don't work. And yes, these checkboxes are actually part of the cell. The checkbox was inserted using Data Validation - Checkbox (tickbox). I also have the option to insert value if TRUE/FALSE and the cell shows the Checkbox but you can see the value in the formula bar too. But when I use the IF function, I end up getting the same error.
Any idea how it's done on Google Sheets?
thanks Dave. I should have expected this from you, as per our altercations in the past. My apologies for not giving all info the world has to offer as the answer. I will go ahead and reply to the OP now if I can.
Last edited by vba_php; 01-01-2021 at 09:19 PM.
Can you please explain what he said?
are you talking to me, zain? in excel, check boxes can be LINKED to cells, and thus, a reference, or sometimes called a ""pointer"" is created. that allows code to be easily written to manipulate the boxes, regardless of what actions you want it connected to.
Yes I was talking to you. Okay I get what you're saying. So I googled on how to link the checkbox to cells but I cant find anywhere how to do it on Google Sheets. Do you know to do it? If this works, then I guess the IF formula should work, hopefully.
are you sure you looked it up? this seems to explain it very well:
https://www.benlcollins.com/spreadsh...eets-checkbox/
does it not?
Yes it does explain it properly and as you can see in the screeshots attached I am doing exactly as it says but I still end up getting an ERROR for some reason which is what I need help with. I have attached a screenshot of the Formula used and the Data Validation applied on Cell E9 where the tickbox is
You may just need to reference E9 not use a formula.
2021-01-02_2-31-52.jpg
I tried that way too but the second I use the IF formula, I get an error. Used cell reference to get "Paid" text in one cell. And then applied the IF formula on the cell with the text. Still getting the same error.
Last edited by AliGW; 01-02-2021 at 04:43 AM. Reason: PLEASE don't quote unnecessarily!
I didn't notice this earlier, the formula used in Post#11 is incorrect(extra quotation and bracket), it should be.
Formula:
Please Login or Register to view this content.
Yeah I forgot to mention, I fixed that formula right after. Still getting the same error.
Last edited by AliGW; 01-02-2021 at 05:11 AM. Reason: PLEASE stop quoting unnecessarily!
Administrative Note:
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
What is the formula you are using that is in error?
The exact same formula that you posted above.
Try this:
=if(F9=TRUE,"Paid","Unpaid")
If it's the same as Excel, then the tick box is just a TRUE/FALSE (1/0) switch, and the formatting you've set is not the underlying cell value.
Last edited by AliGW; 01-02-2021 at 06:44 AM.
I don't get an error with
=if(F9="Paid","Paid","Unpaid")
Post #12 mentions just =E9 would be enough.
Also in Post #11 you are missing a quotation after "Paid
Yeah but in some cases it might be Unpaid too so "=E9" wouldn't be enough.
Basically whenever I use the IF function on Google Sheets, I get this error. I don't understand why. If I give you the link of the sheet, will you be able to see what the problem is exactly?
Did you try my suggestion?
Yes, post a link.
Yes I tried that too and a few other alternatives too but the second I put in the IF function, I get an error. I don't understand why. This exact same formula works on Excel 365 but not on Google Sheets.
Here's the link - https://docs.google.com/spreadsheets...t?usp=drivesdk
If you hover over the formula bar, it tells you that the syntax needs semi-colons:
=IF(F9="Paid";"Paid";"Unpaid")
or:
=IF(B9="Paid";"Paid";"Unpaid")
From the tooltip:
IF(logical_expression; value_if_true; value_if_false)
Example
IF(A2 = "foo"; "A2 is foo"; "A2 is not foo")
About
Returns one value if a logical expression is 'TRUE' and another if it is 'FALSE'.
logical_expression
An expression or reference to a cell containing an expression that represents some logical value, i.e. 'TRUE' or 'FALSE'.
value_if_true
The value that the function returns if 'logical_expression' is 'TRUE'.
value_if_false
The value that the function returns if 'logical_expression' is 'FALSE'.
Last edited by AliGW; 01-02-2021 at 07:11 AM.
Ohhh thank you so much!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks