How can I stop my Sheet Summary from changing the location of a cell when form data comes in?
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!
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!