Using column formula when the formula in row 1 is different to the other rows...

Hey guys, I'd be glad of some help.... I'm trying to create a column formula which uses a piece of data from the row above, which it doesn't seem to like, as it appears that the formula must be created in row 1 for it to work? I want to use column formula as I'm adding to the sheet via a form and want the column to auto calculate. Any ideas, suggestions, work arounds?

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Jonnie White

    When I am faced with your situation I make sure my sheet has [Row ID] so I can reference it. If the rows are going to be moved off to another sheet, then I add an additional helper column that generally I call Index which keeps the top row as row #1 (as long as rows are being added to the bottom. Here's that helper column formula (only needed if rows are being moved off) =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

    Then, I write an IF statement that calls out IF([Row ID]@row=1 (or Index@row, depending if needed), and designate what value it will have. Remember you can reference a summary field if you want to be able to change the Row 1 value easily without having to go into the formula directly. Place the column formula for the rest of the rows as the false of this IF statement.

    Does this make sense? Does it work for you

    Kelly

  • I have the same issue and tried your approach Kelly. It still comes up with a pop up indicating the formula syntax is not correct.


    =IF([Line ID]@row = 1, [Item Counted]@row, [Item Remaining in stock]1 - [Item Taken Out]@row + [item Returned]@row + [Item Added to Stock]@row) is what I am trying to copy to the full column

  • KPH
    KPH ✭✭✭✭✭✭

    This part in bold is why you can't make the formula a column formula:

    =IF([Line ID]@row = 1, [Item Counted]@row, [Item Remaining in stock]1 - [Item Taken Out]@row + [item Returned]@row + [Item Added to Stock]@row)

    It is referring to a specific row in the Item Remaining in stock column. Only formulas that do not reference specific row numbers can be converted to column formulas.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!