# formula referencing cell with formula

Options
✭✭✭✭
edited 12/09/19

Hi folks. I'll do my best in describing my issue.  Trying to create a formula that multiples 2 columns together however getting #INVALID OPERATION error.  Does it have to do with a column that is actually a calculated formula?

Column A: "Effective Time" is a calculated column based on this formula (shortened for this explaination)...=IF([Expected Savings Effective date]2 = "4/1/2018", "12")

Result for this example of Column A = 12

How can I reference Column A in any formula for another column...ex: Column B

Column B: "Test" formula = 2 * [Effective Time]2

Result = #INVALID OPERATION

Thoughts???

Tags:

• ✭✭✭✭
edited 07/13/18
Options

If you have two columns (I'll call them "Tester" and "Testee") and you want to reference the entire column in a function in a third column, it would look like this:

=SUM(Tester:Tester, Testee:Testee)

If you have 12 in Tester and 2 in Testee then it will work and give you 14.

Does this solution work for you?

Another way to reference a specific cell is by doing something like this:

=IF([Q1]1 = "1 - Not at all useful", 1, 0)

In this function, if the column titled "Q1" has the text "1 - Not at all useful" then it will return a 1.

[ColumnName]RowNumber works

• ✭✭✭✭
Options

Thanks for the input however I don't think that will resolve my "#INVALID OPERATION" error

• ✭✭✭✭
Options

If you could post the entire formula for column A that would help but I can see right now that you have it returning "12" - which inputs as a text. Drop the "" and just have 12 like in the IF function I posted.

• ✭✭✭✭✭✭
edited 07/13/18
Options

Wrap your first formula in a =VALUE(IF(etc., etc.))

Smartsheet refers to a text string when displaying the results of a formula. Text can't be used when adding numbers like that. Using the VALUE function will convert the text string that represents the number 12 into the number 12 itself.

You may also want to change up how your dates are formatted in the formula. To reference a date in a formula for Smartsheet you have to use DATE(yyyy,mm,dd).

• ✭✭✭✭
Options

Revised and it worked!!!  It's funny how long you can spend on something when the fix is so simple and right in front of you.  Thanks for your help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!