Tick a check box if conditions are met and another check box is ticked

Ian Smith 2017

I have 4 columns in a smartsheet

Value (number), Credit Checked?(tickbox), Credit Limit(number) and Credit Limit exceeded? (tickbox)

Value is manually entered

Credit Checked is based on a vlookup to another sheet

Credit limit is based on a vlookup to another sheet

Credit limit exceeded should tick if the following conditions are met.

If (Value ">" Credit limit and Credit checked = true then Credit limit exceeded = true else false)

My formula is =IF(AND([Value]@row > [Credit Limit]@row,[Credit Checked?]@row =1,"1","0")

This gives me an unparseable

If I use: =IF([Credit Checked?]@row, "1", IF(Value@row > [Credit Limit]@row, 1, "")) then it ticks credit limit exceeded even if credit checked is not ticked.

For the life of me I can't see what I'm missing.

Any pointers most greatfully received.

Lucas Rayala

@Ian Smith 2017, your example formula was missing an end parend after the "=1". Something obvious, but make sure your formula column is a checkbox column. You can't use quotes around the 1 and 0 when doing a checkbox formula, that turns the 1 and 0 to strings (i.e. to text instead of numbers -- the checkbox works using numbers). I'm surprised you got it to check? This should work:

=IF(AND([Value]@row > [Credit Limit]@row, [Credit Checked?]@row =1),1, 0)

If that doesn't work, check if both your [Value]@row and [Credit Limit]@row aren't coming through as strings. You can point a formula at those values and say =IF(ISNUMBER([Value]@row), "X", "") -- if you see an "X" its a number. Do the same for the credit limit. If either is not a number (I.e. they came through as strings) then you need to work on that.