Creating a formula that references a static cell?

Jennifer Whitlock · 08/16/19

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!

Andrée Starå · 08/16/19

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]1

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Jennifer Whitlock · 08/16/19

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!

Jennifer Whitlock · 08/16/19

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!

Andrée Starå · 08/17/19

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.

Paul Newcome · 08/19/19

Have you tried a MAX function, or do the dates not correspond with the entry order?

Jennifer Whitlock · 08/21/19

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

Paul Newcome · 08/21/19

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.

Andrée Starå · 08/22/19

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

Sandra Murray · 11/08/22

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?

Shelby · 12/17/22

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)