Absolute Reference in Cell Formula
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
Answers
-
[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
-
@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
-
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?
-
@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.
-
Nono, the formula will autofill
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!