formula referencing cell with formula

KevinL

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???

mwiggins

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

KevinL

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

mwiggins

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. 

Paul Newcome

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

 

KevinL

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!