Formula changes when new form is submitted

stephennazworth

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!

Devin Lee

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.