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!

Find more posts tagged with

Forms

Formulas

Comments

Andrée Starå

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

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

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å

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

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

Jennifer Whitlock

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

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å

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

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

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)

Jason.Darr

https://community.smartsheet.com/discussion/comment/354762#Comment_354762

@Shelby - Excellent suggestion! This solved my problem.

CAS the CSA

https://community.smartsheet.com/discussion/comment/198476#Comment_198476

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.