Absolute Reference in Cell Formula

Bethany Garcia

I'm creating a sheet where I would like the user to input three values (property name, old property code and new property code) one time, and then I want a helper column to concatenate those values as a column formula so it applies it to every row in the sheet. I will then have an automation that automatically sends the rows that are assigned to the appropriate contact with other information from the row, but I also want the row grid in the email to show the property name, old property code and new property code.

I can't figure out how to get the property name/code field to populate with the value. The formula I'm using is below, which is looking for the value @row and since that value only exists in the first row it's not picking up for the other columns. I tried reference [Property Name]1 instead of @row but that won't work.

Any ideas to get the values into the rows - or even better, is there a way to have the property name, old and new property code fields populated dynamically in an automated email message vs me having to add it as a field for the table embedded in the email?

="Property Name: " + [Property Name]@row + CHAR(10) + "Current/Old Property Code: " + [Old Property Code]@row + CHAR(10) + "New Property Code: " + [New Property Code]@row

2023-04-11_20-01-48.png

BullandKhmer

[Property Name]@row = This row

[Property Name]1 = relative ref.

[Property Name]$1 = absolute row, relative col

$[Property Name]1 = absolute col, relative row

$[Property Name]$1 = absolute col, absolute row

You can also do absolute & relative ranges, i.e.

[Property Name]$1:[Property Name]@row

Bethany Garcia

@BullandKhmer When I use those within a single cell they work to reference the specific cell/column I want, but when I try to turn that into a column formula, or use it in a column formula, I get an error. Is there a way to use those reference formulas within a column formula? The formula below is ultimately what I want to use as a column formula, and the [Property Name], [Old Property Code], and [New Property Code] references would not be @row but would be the reference to the specific cell/column.

="Property Name: " + [Property Name]@row + CHAR(10) + "Current/Old Property Code: " + [Old Property Code]@row + CHAR(10) + "New Property Code: " + [New Property Code]@row

BullandKhmer

Yeah, you cant use certain references in column formulas, I think anything with an absolute and something else (I dont know, look it up). Cant you just write the formula in row 1 and drag it down?

Bethany Garcia

@BullandKhmer I could, but I wanted it to be a dynamic column formula so that if new rows were added we didn't have to manually add the formula or value to that row. That'll probably be what I have to do though, put the cell reference into each row and just add it in anytime we add a new row to the sheet.

BullandKhmer

Nono, the formula will autofill