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

Jonnie White

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?

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

R Mady

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

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.

R Mady

Yes, that I understand. What I am asking is if there is a workaround for this. Is there a way to make it a column formula instead of having to copy it down the column manually?

Thanks

Andrée Starå

Hi @R Mady

I hope you're well and safe!

Yes! You can add/replicate the [Item Remaining in stock]1 part in a Sheet Summary field, and then reference it in the formula, and then convert it to a column formula.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

KPH

Hi @R Mady

@Kelly Moore 's insightful solution used the IF line ID to enable Jonnie to create a column formula that did something different on row 1 to the other rows in the column. You need this as row 1 cannot reference the row above, as that row does not exist.

It looks like you also want to use a value from row 1 on other rows (possibly all other rows, possibly only row 2). And you want to do this with a column formula. But column formulas do not allow you to refer to a specific or relative row. That’s the next step.

If you want to use the value from a particular row on all rows, you could put the value into a sheet summary cell, then refer to that cell instead, as @Andrée Starå suggests. Your formula would be something like the one below, and could be a column formula.

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

Sheet summaries are explained here, if you aren't familiar with them: https://help.smartsheet.com/learning-track/level-2-intermediate-users/sheet-summary

If you want to use a value from the row above each time, not specifically row 1, for example row 3 uses [Item Remaining in stock]2, row 4 uses [Item Remaining in stock]3, etc., then we’ll need another step - using a MATCH function on the line ID to find the row.

Could you explain your use case?