formula referencing cell with formula

KevinL
KevinL ✭✭✭✭
edited 12/09/19 in Formulas and Functions

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:

Comments

  • mwiggins
    mwiggins ✭✭✭✭
    edited 07/13/18

    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
    KevinL ✭✭✭✭

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

  • mwiggins
    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
    Paul Newcome ✭✭✭✭✭✭
    edited 07/13/18

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!