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

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.

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 02/24/23

    @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!