Creating a formula that references a static cell?
Hello,
I have a form that is submitted to one of my sheets daily. Currently, new entries populate at the top of the sheet. I am using a formula that always needs to reference the top row, or newest entry. Is there a way to accomplish this? Currently, if a new entry is submitted, the formula changes to tracking row 2 instead.
Thanks!
Comments
-
Hi Jennifer,
You'd use an Absolute Reference for that.
More info: https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula
Absolute reference (always refers to that specific cell, row, or column)Type a $ symbol in front of the column name, row number or both.
=$[Column A]$1
=[Column B]$1
=$[Column C]1Hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andrée, Thanks for your feedback. Unfortunately, I've tried that, and perhaps my wording was off, but that doesn't solve my current issue.
When a new form is submitted, the new line is input at the top of my sheet. I want to be able to always reference the most recent submission. So, I don't want it to reference one specific row, but rather the newest row.
Please let me know if that helps to clarify the issue I am trying to solve.
Thanks!
-
I'd love any input on possible work arounds here! We need to be able to reference the date of the latest form submission at the top of the sheet.
Thanks y'all!
-
Happy to help!
Ok.
I might have a solution for this as well. I'll get back to the post later and hopefully with the solution.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Have you tried a MAX function, or do the dates not correspond with the entry order?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul,
Thanks for your suggestion! This worked.
Unfortunately, it didn't solve the issue I was hoping it would, so it's back to the drawing board for me.
Thanks again
-
What is the exact challenge you are facing? Sometimes putting your end goal out here can provide an overall solution that goes a completely different route.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Happy to help!
I saw that Paul answered already! That was what I was thinking about, as well!
I also agree with Paul, can you describe your need/goal in more detail?
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I have the same issue - I want to pull the most recent status report text to a dashboard. When using the absolute reference, it moves as new rows are added.
Perhaps I can write a report to only display one line?
-
Jennifer, this may not solve the exact issue you had but it does help to reference a particular cell at all times. Input the INDEX function below - insert the "Column" and row locations you'd like to reference. It appears to work for me.
=INDEX(Column:Column, 1, 1)
-
@Shelby - Excellent suggestion! This solved my problem.
-
regarding the =$[Column A]$1 option
If I use =$[Column A]$@row it seems to break the cell link in the formula, is there another way around this?
If I use =$[Column A]@row it comes up with a syntax error and I cannot make it column formula
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 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
- 290 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!