@row referencing cell above
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.
Best Answer
-
A process I like to use to solve for these kind of issues is to create 2 columns:
- "LINE-ID" : Auto Number Column
- "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)
Answers
-
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.
-
A process I like to use to solve for these kind of issues is to create 2 columns:
- "LINE-ID" : Auto Number Column
- "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)
-
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!
🤩
-
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!
-
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?
-
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.
-
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.
-
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.
-
Hello everyone,
Has anyone found a solution for this issue yet?
Thanks,
-
*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")
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives