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
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!