IF Function

edited 12/09/19 in Formulas and Functions

I want to create an IF condition where if a cell contains a symbol on another sheet that equals "New" then populate that value otherwise, leave it blank.  I have this formula:

=IF({New Applicant(s) Range 1} = "New", {New Applicant(s) Range 2})

According to what I've read in the HELP, the False condition is optional and if left blank, then a Blank is returned, which is what I want.  However, I get an error #INVALID OPERATION.  

Can anyone determine where I'm off?





  • You shouldn't NEED a false parameter. 

    I don't think your 'false' condition is causing the problem, but you can test that by telling it to return nothing with an empty string. ""

    the following examples work the same way. 

    • =IF([Column2]1 = "New", [Column3]1 )
    • =IF([Column2]1 = "New", [Column3]1, "")

    I am guessing that your problem is elsewhere in your function. 


  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    As you are creating named ranges visible only to you, I'd suggest removing the brackets (e.g. {New Applicants Range 1}).

    Although the FALSE parameter is indeed optional, no harm in throwing in a "" to cover that eventuality.

    Kind regards,

    Chris McKay

  • Yes, you are correct.  I think I need to review how to handle ranges of cells a bit more.  I'm new to Smart Sheets and struggling to convert my Excel brain to this language.  Thanks for the tip!


  • Chris,

    You bring up a point that I need to study which is user permissions and ranges.  I appreciate the tip. 

    Thanks very much,


  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    My pleasure Mike

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!