@row referencing cell above

Kirstine

Is there a way that I can use @row to reference the cell above? I'm creating a formula to check for duplicates and I want to compare details in the current row to the one above.

Leibel S

A process I like to use to solve for these kind of issues is to create 2 columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

This will allow you to run a check on a previous row value by using Index(column:column, [ROW#]@row -1)

Mark Cronk

Hi @Kirstine ,

I can't find a way to do it with @row without a reference to a specific row too. There are lots of posts in this community about how to find and flag duplicates.

Curious if someone else has a better answer.

Mark

Leibel S

A process I like to use to solve for these kind of issues is to create 2 columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

This will allow you to run a check on a previous row value by using Index(column:column, [ROW#]@row -1)

Kirstine

Thanks @Mark Cronk and @Leibel S for your input!

Leibel your clever trick has done the job! I don't really understand it yet (I've just plugged in the formulae with my column names) but I'll wrap my head around it soon! Thanks so much!

🤩

Frank Falco

Thanks @Leibel S, that is a good solution that will work until we have a supported command.

LakeWaconia

Mark and Kirstine, this is just what I've been looking for, however, I keep getting an #UNPARSEABLE response. I have the "LINE-ID" AND "ROW#" columns working fine, however whenever I add an "=INDEX([Customer],[ROW#]@row -1)" either by itself or within a longer formula, I get #UNPARSEABLE .

Any ideas?

Mark Cronk

Try:

=INDEX([Customer]:[customer], match([ROW#]@row -1, row#:row#))

Work?

Mark

LakeWaconia

Thanks Mark, I really appreciate the support. I still get an #UNPARSEABLE statement.

At one time I thought for sure I had your original solution working, so I'm wondering if something else is messed up in my sheet? I've attached it for reference.

Grip-Tite Stamping Pitch Build Board.xlsx

See formulas in "Customer" column

Stephen Hindes

Mark Crok, i like this formula, but i can not get it to work.

=IF(date@row <> "", (IF(date4 = date@row - 1, Mds4 + mods@row, mods@row)), "")

i am trying to compare date in row 4 with row 5. it come with a False,

if i remove the "@row-1" and use "4" it work fine. =IF(date@row <> "", (IF(date4 = date@row, Mds4 + mods@row, mods@row)), "")

but i am trying to covert the column into a formula and it does not like the reference to a number.

any idea?

Jamie Bedford

This is a pretty slick solution, but a word of warning if you're hoping to use this in sheets with relatively large numbers of rows:

This kind of full-range matching apparently can cause Out Of Memory errors. https://community.smartsheet.com/discussion/86274/error-when-running-out-of-cpu-memory

Having a function like Excel's "OFFSET()" might significantly reduce the processing requirements for this kind of functionality by not needing to scan hundreds/thousands of rows just to look one row above.

SWAus

Has anyone successfully managed to get the specific cell above within the same column? I have the auto column and then row column working, when I have a third column referring to a forth it works perfectly but when I switch it reference itself/the third column it doesn't work and I get an #INVALID COLUMN VALUE.

The lack of ability to reference above natively seems a massive oversight.

morass

Hello everyone,

Has anyone found a solution for this issue yet?

Thanks,

L_123

*Apparently I didn't hit submit when i typed this a while ago. Here is a solution:

So the suggested formulas have a problem, if you switch the order of the rows in any way, it will no longer work. You can fix this by instead of searching for the number previous, removing one from the current.

=index(returncolumn:returncolumn,match(autonumber@row,autonumber:autonumber,0)-1)

I assume you are attempting to set this as a column formula, which would cause an error in the first row every time. As such I would wrap an iferror on it.

=iferror(index(returncolumn:returncolumn,match(autonumber@row,autonumber:autonumber,0)-1),"First Row")