@row referencing cell above

Kirstine
Kirstine ✭✭✭✭✭✭
edited 07/26/21 in Best Practice

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.

Tags:

Best Answer

Answers

  • Mark Cronk
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Kirstine
    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
    Frank Falco ✭✭✭✭✭✭

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


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • LakeWaconia
    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
    Mark Cronk ✭✭✭✭✭✭

    Try:

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

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • LakeWaconia
    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.

    See formulas in "Customer" column

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

  • 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
    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
    morass ✭✭

    Hello everyone,

    Has anyone found a solution for this issue yet?

    Thanks,

  • L_123
    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")