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.

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.