IF Function

Hi All,

I copied an IF function to another sheet with identical column names. For some reason, the function no longer works. Does anyone know if there were changes to the IF Function. Here is the formula that I copied:

=IF([Start Date]3 < [Baseline Start]3, "red")

I got an "#Invalid Operation"

I submitted a help ticket but haven't gotten a reply. Does anyone here know?

Thanks.

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

Have you double-checked the column types and names?

Here's more information about the error message.

#INVALID OPERATION

Cause

Operators (see Create and Edit Formulas for details on acceptable operators) in a formula aren't supported in Smartsheet or are mistyped. For example, this IF statement that returns the text "Low Stock" if a cell contains 25 or less...

=IF(Quantity1 =< 25, "Low Stock")

...has the "less than or equal" to operator in the wrong order; the correct order being <= to make the formula =IF(Quantity1 <= 25, "Low Stock")

Resolution

Check all operators to make sure they're not mistyped and are supported by Smartsheet. See Create and Edit Formulas for details on acceptable operators. The most common cause of this is typing <= (less than or equal to) and >= (greater than or equal to) operator combinations in the wrong order.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Queenie Yap

Thank you Andree for responding. Column type, column names and formulas are identical in both sheets. For some reason the formula doesn't work in the newer sheet. Just wondering if the programming was changes for the functions because there was an update done a few weeks ago.

Queenie

Paul Newcome

I have had this happen before as well. If you edit the formula in the new sheet (remove the row reference or delete the first letter of a column name or something) to "break" it, leave the cell, the edit the formula again to "fix" what you "broke", you should be good to go.

Andrée Starå

That's strange!

I'd be happy to take a look and see if I can see what's causing it.

Can you maybe share the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Andrée Starå

Yes, I agree with Paul!

If you haven’t already, I would recommend that you try to break/fix the formula.

Queenie Yap

OK, I figured it out. Both columns need to be the same column type. One of the columns I had it as TEXT column when both columns need to be DATE columns. Thanks for everyone's comments.

Queenie

Andrée Starå

Glad you found the solution!

Happy to help!

Paul Newcome

Yep. That'll do it. Glad you were able to get it working.

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.