If and checkbox

I have used th IF equation and the checkbox and I want it to copy a value from another cell when the checkbox is checked. Until here I am ok. However I want it to apply the number "0" (zero) when the check box is unchecked. can anybody help me please?

Thank you.

Find more posts tagged with

Conditional Formatting

Comments

Atus Bartal

Dimitris,

Have you tried something like this?

=IF(Checkbox_column1, Value_column1, 0)

[if the checkbox in the 1st row of Checkbox_column is checked, it gives the value in the 1st row of Value_column; otherwise - if not checked - it gives zero.]

Atus

JohnHinkle

Setting the result to 0 will just show an empty checkbox. (1 = checked, 0 = not checked). If you want display an actual 0, add quotes around the zero in the formula Atus suggested, like this:

=IF(Checkbox_column1, Value_column1, "0")

Atus Bartal

Sorry, I wasn't clear. I put the formula in a new (third) column (Text/Number type). The check box just serves as a kind of switch to put a number from the Value_column to the Result_column (third colunn).

Dimitris Papandreou

Thank you Atus and John,

If I put only one IF in my equation, it works, however my problem is that if the result of this column I want to add it in the next one (sum) and when the checkbox is not checked in the sum column it gives me an invalid or blocked message. I have tried many different solutions but I haven't managed to correct it!

What I want to do is in the first (number) column to write a number and then in the next next numbe column write the income I have and in the next one, if I have the checkbox checked, put the value of the first one and if not checked put zero. In the next column I have the difference between income and costs.

I have attached some screen shots

Dimitris Papandreou

I have made it work if I use only one IF, both with checked and not checked, however I cannot make it work with both cases in one equation.

Atus Bartal

Dimitris,

What I first see is that the syntax of your IF formula is wrong, it misses ", 0" (the "else" part) from the end.

Syntax: IF(logical_test, value_if_true, value_if_false)

Check out Smartsheet help for further info: http://help.smartsheet.com/customer/portal/articles/775363-using-formulas#logic

If you still have problems, please copy the formulas you use in each related colunn here in the discussion and we'll try to help.

Dimitris Papandreou

Hi Atus,

I tried but I didn't make it and I decided to make it a little more manual.

Thanks for your help.

Dimitris

J. Craig Williams

Dimitris,

Can you elaborate on "a little more manual"?

Craig

Dimitris Papandreou

Thank you for your interest Craig.

I have in a column the expenses that I have to take care of and in the next two columns I have all the incomes and expenses that I have received and paid. On the next column I have a column that calculates the what is the rest of the money I have to make other payments. I wanted to have a checkbox that when I would check it, it would copy the expense to take care of to the column with the expenses that I have paid. However when I do this, calculation in the next column does not work, so now I do manually and copy the number from the first column to the second by writting it.

J. Craig Williams

So 5 columns

A Expenses Due - manual entry

B Incomes - manual entry

C Expenses Paid - manual entry?

D Remaining Expenses to be paid - = A - C

E Check box Paid in Full

Are you trying to populate C when the checkbox is checked but not when it is unchecked?

Craig

Dimitris Papandreou

Thank you Craig for your help.

Yes! That's exatly what I want to do. But when I have the If equation in column C, the Remaining expenses from column D does not work for some reason I cannot find.

In some tries, I made it work, but if I had not checked a raw, the next raws did not work.

Dimitris

J. Craig Williams

If you add a formula in column C, as soon as you enter data* then you have overwritten it.

I interpret the process as

A - total to be paid

C - might be partial payment (you want to enter an amount

E - check box for 'just pay it off' and save typing.

but that won't work for the reason stated above.

Craig

Dimitris Papandreou

Ok! I understand, but this way, I will never easily be able to the what is my balance in order to be able to know which other payments I can make.
Is there another way doing that?

Dimitris

J. Craig Williams

Dimitris,

At the simplest, I would just type into column C instead of trying to save a few keystrokes.

From the images you provided previously, I don't see any dates or interrelation between the rows. Is there?

If there are, you might try to think vertically instead of horizontally.

row 1 - summary row

row 2 - expense row

row 3 - payment row #1

row 4 - payment row #2 (if needed)

etc...

for each expense.

I gotta say that if I look at your images, they are mostly Greek to me.

https://en.wikipedia.org/wiki/Greek_to_me

When you refer to the 'balance' do you mean remaining to be paid for each specific expense?

Craig

Dimitris Papandreou

Hahaha!

Thank you once more for your help!

No there is no date interelation, so you promose me to have it as parent and children raws with the summary raw been the parent and the rest, the children ones?

I mean the total Balance. As I have it now it counts all incomes and outcomes and it gives me the total balance.