Formula changes when new form is submitted
I'm working on a project where metrics in my dashboard automatically change when a new form is summited. I've done this by having my metric use a sheet summary which includes a simple reference formula.
The problem is whenever a new form is submitted, the formula changes because a new row is created. I only want my formula to reference the first row that way when new data comes in, it will be referenced.
For instance my formula is: =[Assisting Carrier]1 which pulls the data from row one. However, when a new form comes in, my formula changes to =[Assisting Carrier]2 because a new row is added. Is there a way to keep my formula from changing?
Is there a way to get around this? Thank you!
Best Answer

You'll need to use a formula to pull in the latest information and reference that formula cell.
Insert a new column with the column type Auto number
At the bottom of your sheet (assuming you have a top entering form) insert the following formula into each column which will pull the most recent entered values.
=INDEX([Column Name]:[Column Name], MATCH(MAX([Row ID]:[Row ID]), [Row ID]:[Row ID], 0), 0)
Then you can reference that cell directly within your sheet summary.
Note: You will need to have a new value entered after inputting the formulas because it will become the highest value Row ID and that should be data and not the formulas.
Answers

You'll need to use a formula to pull in the latest information and reference that formula cell.
Insert a new column with the column type Auto number
At the bottom of your sheet (assuming you have a top entering form) insert the following formula into each column which will pull the most recent entered values.
=INDEX([Column Name]:[Column Name], MATCH(MAX([Row ID]:[Row ID]), [Row ID]:[Row ID], 0), 0)
Then you can reference that cell directly within your sheet summary.
Note: You will need to have a new value entered after inputting the formulas because it will become the highest value Row ID and that should be data and not the formulas.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 409 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!