How can I stop my Sheet Summary from changing the location of a cell when form data comes in?

ivanlopezb

I am creating an inventory form that populates data into a sheet on "Top of Sheet". I want to create a summary sheet that reflects how much of an item we need vs what we have entered on the count (via the form).

My issue is, whenever a form gets filled out, it also shifts the cell I am trying to reference down. I want it to always reference the same cell whenever new data from the form populates to the top.

300 references the amount of towels we need at all times

=300 - [Drying Towel]$1 (I always want it to reference cell 1 when new data comes in)

Issue, the number after [Drying Towel] shifts every time new data is available. =300 - [Drying Towel]$2

What am I doing wrong? Is there a better way?

Thank you!

Genevieve P.

Hi @ivanlopezb

The cell reference says with that specific cell, versus that row. That means that as new rows are inserted the formula will follow the cell down the sheet, as you've described.

What I would do in this instance is add a Created Date System column to the sheet. Then you can use MAX to find the Max date in the sheet (the newest date), and use that to then bring back the correct cell.

Try something like this:

=300 - INDEX([Drying Towel]:[Drying Towel], MATCH(MAX(Created:Created), Created:Created, 0))

Cheers,

Genevieve