Absolute Reference changes if cell is moved

SummerS

Hi,

Looking for help with Absolute references in cell formulas. I need the formula reference to remain the same even if the user moves or copies the cells to a different column.

The scenario is Column8 has the formula =SUM([Column 6]@row, [Column 7]@row)

If a user moves the Column6 cell to Column9 , the reference in the formula also updates to Column9 i.e. it becomes =SUM([Column 9]@row, [Column 7]@row)

The goal is to have the reference remain Column6. I have tried all the Absolute Reference combinations but none have worked.

jmyzk_cloudsmart_jp

Hi @SummerS

Thank you for the clarification! I did not know the phenomenon.

I suggest using the column formula to keep summing Columns 6 and 7 at Column 8, regardless of the user's cell moves.
To my surprise, the display value of the column formula changed when I moved the cell in Column 6 to Column 9 in the first row. However, as shown in the fourth row, the column formula keeps adding Columns 6 and 7 to Column 8.

https://app.smartsheet.com/b/publish?EQBCT=d6f5cad21f584b56a893c378fe9eec0f

kowal

hi @SummerS ,

there is not absolute Reference like in excel ($….) have you considered using calculations in another sheet with celllinking or changing ranges? Once you link the cell between sheets then it works like Absolute Reference.

Hope it helps.

jmyzk_cloudsmart_jp

Hi @SummerS

What do you mean by "a user moves the Column6 cell to Column9"?
I was not able to recreate the formula change;

=SUM([Column 9]@row, [Column 7]@row)

In the image below, in the second row, when I moved the [Column 6] cell to [Column 9] by cut and paste, the formula did not change, and the SUM changed from 3 to 2.

In the 5th row, the formula has an absolute reference like $[Column 6]$2, and the SUM value changed from 3 to 2 when I moved the [Column 6] cell value in the second row to [Column 9].

https://app.smartsheet.com/b/publish?EQBCT=73f9a82886fe426e92aa40f74c76ca52

SummerS

Hi @jmyzk_cloudsmart_jp !

By moves, I mean dragging the Column6 value to Column9 and thus leaving Column6 empty. See the picture for the cursor type. (it's not dragging the value across all columns but moving it)

Users have done this multiple times instead of cut/copy-paste and it changes formulas in locked columns.

jmyzk_cloudsmart_jp

Hi @SummerS

Thank you for the clarification! I did not know the phenomenon.

I suggest using the column formula to keep summing Columns 6 and 7 at Column 8, regardless of the user's cell moves.
To my surprise, the display value of the column formula changed when I moved the cell in Column 6 to Column 9 in the first row. However, as shown in the fourth row, the column formula keeps adding Columns 6 and 7 to Column 8.

https://app.smartsheet.com/b/publish?EQBCT=d6f5cad21f584b56a893c378fe9eec0f

SummerS

Had to rearrange some things but using the column formula worked.

Thank you!!

jmyzk_cloudsmart_jp

Happy to help!😁