formula referencing cell with formula

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

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…