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?

mrsci3ntist

Hi @KPH,

Piggybacking on this one, if I am looking to simply increase row above by +1 if a condition is met, would the best solution be your recommendation to use the MATCH function? How would this look using MATCH? Example of what I'm trying to achieve with my column formula here, where this column is titled ProjectNum:

=IF(COUNTIFS(Project:Project, Project@row, [Row ID]:[Row ID], <=[Row ID]@row) = 1, row above +1, value of row above)

Essentially, the sheet begins with X number of inputs (rows) having the same Project name(column formula in ProjectNum must return 1 in the first row as well!) → once the Project name changes at any given row, that new Project returns ProjectNum = 2… and should the same Project name be used for the following five rows the ProjectNum remains at 2, until the Project name changes again, where it inherits ProjectNum = 3 using the formula... and so on.

I'm sure there's an easy solution, thanks in advance for your help!

KPH

Hi

I've mocked this up to make sure I understand what you are doing. The data looks a bit like this, right?

And you are using COUNTIFS to see how many times the project name is mentioned previously so that you can create a project number when this is the first mention?

In my example the COUNTIFS would look like this:

=COUNTIFS(Project:Project, Project@row, [Row ID]:[Row ID], <=[Row ID]@row)

And your intention is to use an IF function to return the project number from the row above where the COUNTIFS is anything other than one and add one to the number in the row above when it is one.

This will create a circular reference as the value in the cell is being used to change itself. How about using a COUNT DISTINCT instead? This formula will count the number of unique values in the Project column between row 1 and the current row:

=COUNT(DISTINCT(Project$1:Project@row))

It looks like this:

Would that work?

The limitation is that if there is a row for a previous project added at the end, the project number would not be correct. But it sounds like that isn't a situation that will occur.

It also cannot be a column formula.