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.
A process I like to use to solve for these kind of issues is to create 2 columns:
This will allow you to run a check on a previous row value by using Index(column:column, [ROW#]@row -1)
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.
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
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 .
=INDEX([Customer]:[customer], match([ROW#]@row -1, row#:row#))
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.
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.
Has anyone found a solution for this issue yet?
*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.
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.
Up until today I've restricted my understanding of datamesh to copying values from sheet to sheet. However, I just discovered today that you can use it WITHIN a sheet! What does this solve? If you ever need to copy a value from column A to column B, but do not want to use a formula, then this process is perfect. It also…
Here is a way to create reminders for Proof reviewers. This solution will: Send a reminder 5 days after the initial invite, if no one has responded to a Proof review request. Send a reminder 5 days after the last reminder, if no one has responded to a Proof review. Reset when a new Proof version is created. Some useful…
This initial solution is based on form submissions being made and each form submission triggering the creation of a set of "template" child rows per submission WITHOUT the need for a 3rd party app, the premium add-on Bridge, or the API. So if your form submissions look like this: Your working sheet could look like this:…
©2023. All Rights Reserved Smartsheet Inc.