Locking cell references in a formula

RBro79

I want to copy/paste cells with a formula that needs to move with the paste, however one of the references needs to stay pointing at a static column. In Excel, I would use $A1 as the cell reference. In Smartsheet, I don't see a way to do that.

Paul Newcome

It is the same in Smartsheet. Use a $ to lock in an absolute reference whether that be before the column name to lock in the column, the row number to lock in the row, or both to lock in both.

$[Column Name]1

[Column Name]$1

$[Column Name]$1

Kerry St. Thomas

In Smartsheet, it's as easy as naming the column. In Excel, column A is always the first column and Column B is always the second column, even if you rearrange things. In Smartsheet, the name is the name is the name, regardless of how you arrange your sheet.

In terms of how to reference similar to $A1 (locking to a COLUMN but not a ROW)… consider this example. Column1 is your Primary column - which you might rename to "Order Number", Column 2 might be renamed to "Sales Amount", and so on. Assume that you want to compute the tax (which let's pretend is 5%) on each line. You could then create a column called "Tax"… and then another column that's the sales amount + tax, called "Total". The formulas would be:

Tax: =[Sales Amount]@row*0.05
Total: =[Sales Amount]@row + Tax@row

Doesn't matter where you put "Tax" or "Total." The formulas will work. If you rearrange the columns, the above will still work. (Though you'll also run into issues if you use things like VLOOKUP, but that's a whole other topic.)The whole @row thing is essentially a variable that means "find the value ON THIS ROW for whatever column… and then do things. Also notice the square brackets [] — the reason for that is the SPACE in the column name. If there's anything but letters in the name, you need to put the square brackets around the column name; if it's just letters, the brackets are optional.

Here is a bit more on @row:
https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

https://community.smartsheet.com/discussion/100275/how-to-index-match
community post about INDEX/MATCH

Good luck!

RBro79

Hi Kerry,

I name all my columns. I have a sheet that I was copying a cell with a formula in it to move it to another area. The columns repeat ([Change Date 1]@row, [Change Date 2]@row, etc.) so if I copy the cell and paste it to the right, all the references are relative and update to their new locations. That's exactly what I want it to do…except for one cell reference (e.g. [Initial Date]@row). I need that reference to remain the same with each new paste.

The workflow is either to copy/paste the cell and go back to fix the one reference in each cell (this is replicated 20 times). Or, I open the cell and copy/paste the formula, but then I have to go back and fix the other references that I want to change with each paste.

In Excel, there's a way to lock a single reference in a formula that is otherwise relative to the location of the cell where the formula is moved to.

I'm sure there is a better way to explain what I'm doing, but I'm not sure how. I ended up pasting the cells into my sheet and going back to fix the one reference in each cell. It was painful and I'd love to not have that issue in the future.

Paul Newcome

It is the same in Smartsheet. Use a $ to lock in an absolute reference whether that be before the column name to lock in the column, the row number to lock in the row, or both to lock in both.

$[Column Name]1

[Column Name]$1

$[Column Name]$1

RBro79

Now I feel dumb that I didn't just try that. Thank you!

Paul Newcome

Happy to help. 👍️