#Invalid Operation in basic formula in some cells - please help

Jae Wyld

Hi everyone,

I need some help with a helper column I created to resolve an issue.

I am currently pulling together 4 non-adjacent multiple-contact columns into one string by using =[column1]1 + [column2]1 + [column3]1 + [column4]1 in a text/number column which displays all the names in one string (thank you @Paul Newcome!)

This is successful for the vast majority, however I've identified a few cells that display #INVALID OPERATION.

I've looked at the contact column cells and nothing is different from these compared to to the other cells. Some of the cells are blank, however this is the case for other rows where the formula is successful.

Could anyone please advise what might be causing the problem?

Thank you for your help,

Best wishes,

Jae

Andrée Starå

Hi Jae,

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

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jae Wyld

Hi, I;m afraid I cannot screenshot as the whole sheet contains sensitive data - the cells I'm viewing the error on are a single string of multiple names. Can you please advise why #INVALID OPERATION would be displayed in a minimal number of cells where the formula used works 95% of the time, and there are no issues / differences between the cells referenced in the formula?

Thank you

Andrée Starå

Happy to help!

Have a look here.

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--invalid-operation

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

Brad Powers

I am experiencing the same problem and hopefully sharing some screenshots of my set up will help us both resolve this issue.

The first column [Brand Category ID] is a text field. The second column [Category ID] is using a VLOOKUP formula to pull a text value from an external sheet. The third column [category_ids] is using a simple concatenation formula to join the values of these two fields (not a join formula because these fields are moved around on the sheet and are not usually next to each other):

=[Brand Category ID]@row+","+[Category ID]@row

The formula is working great for all but that single cell on this small set of test data. Before I apply this solution for hundreds of rows, I need to work out this error.

What would cause a single cell to throw an error with such similar data when it is working as expected for the rest of the column formula?

Genevieve P.

Hi @Brad Powers

I'm wondering if the formula is getting confused adding numbers together, thinking you want it to be numbers and not text.

Try adding some parentheses to ensure the first ID is seen as text before adding it to the second cell:

=([Brand Category ID]@row + ", ") + [Category ID]@row