IF Function

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?

Thanks,

Mike

Find more posts tagged with

Formulas

Comments

bdchapin

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

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

mmcgibbony

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!

Mike

mmcgibbony

Chris,

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

Thanks very much,

Mike

Chris McKay

My pleasure Mike

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…