Absolute row reference changes on the first row if moved OR if another row is moved to first row

Terry Ables

I'm trying have have my rows automatically update priority while allowing the user to move the rows to arrange their desired priority order. Its all working good with the exception if i move the first row or try to move another row as the new first row. I have absolute reference to the row, but it changes it anyway.

The formula I'm using.

=COUNTIFS(Status$1:Status@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

If I move the first row, the formula changes and the absolute row number changes to the row I moved to.

=COUNTIFS(Status$NEWROW#:Status@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

If i move another row to the first row the formula changes and the absolute row number changes to 2.

=COUNTIFS(Status$2:Status@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

An absolute row shouldn't change. I need help with what I'm missing.

Thanks,

Terry

Alejandra

Hi @Terry Ables,

I also experienced the same behavior. I believe this is happening because you're moving the entire row and not just the cell containing the formula. However, I'll double-check with our Support team just to be sure and I'll let you know what I find.

Paul Newcome

I agree with @Alejandra. I just couldn't think of a way to explain it that would make sense.

Basically, when you use an absolute reference, you are locking in that row and not necessarily the row number. Just the row itself. This is how it has worked since I started using SS a couple of years ago.

Generally when I need an absolute reference on row 1 but need the flexibility of sorting/re-ordering, I will leave a note in row 1 that lets people know they need to leave it in place and the "top of the sheet" is actually row 2.

Terry Ables

@Alejandra and @Paul Newcome

Thank you for the feedback.

In the example I provided it does require that the entire row move based on priority. I have tried the "do not move this row" approach. It's not my preferred choice, but does get the job done.

For now I've decided not to use an actual priority numbering system, but just rely on the order in which the list is built. This way the user simply moves the row into its new priority without the worry of the above mentioned.

Cheers,

Terry

Alejandra

Hi @Terry Ables,

Turn out this is expected behavior as it only occurs when the absolute references target their own row. Since the entire row is moved, they continue targeting the same cells.

This would be a great opportunity to submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet. 

Low Par

Hello @Terry Ables and @Alejandra and @Paul Newcome .

Was anything ever done about this issue? From 2020?!

When moving rows you'd expect an absolute reference to be as the name implies (absolute).

I'm trying to allow users to move rows copied from a form sheet into a priority order, and the formula I'm using with an absolute reference does NOT work.

Every time I move a row, the formula changes, and this is not just when moving the *first* row. I even tried cut/paste the row(s) to move over the existing row, and the formula still changes.

Here is how the very basic formula changes when I move rows in the range.

=SUM($[01-ART-CI]$9:$[01-ART-CI]$12)

=SUM($[01-ART-CI]$9:$[01-ART-CI]$11)

=SUM($[01-ART-CI]$14:$[01-ART-CI]$10)

It's very frustrating to have to tell my boss that we're going to have to use Excel after how much I've praised Smartsheet as a comprehensive solution. BTW, I did test this in Excel and it maintains the absolute ref no materr where I move the rows.

If you have a workaround, I'm all ears.