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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…