Google Form keeps changing Spreadsheet cell entry

Question

I seem to be having a bit of a problem, I'm using Google Spreadsheet and have created a form that people would be able to fill in online. It sends that data to a sheet called 'Raw Entry Form', I then take that data and by using

'RAW Form Entry'!C8

I put the data into another sheet. However every time someone fills in the form and it updates that data the cell formula changes from say 'C8' to 'C9', the more forms, the bigger the number gets.

I believe it may be due to the form adding a new row each time someone enters new data, thus moving the cells down and increasing the number.

Does anyone know how to fix this?.

Answer 1

The problem was due to Google Forms adding a new row every time.

If you are having the same problem here's what you need to do to fix it:

Where you previously had your formula change it to:

=arrayformula('YOUR SHEET NAME'!A2:A)

Change the sheet name, and enter the cell you want to target, as-well as the column.

Make sure you delete all other formulas in that column.

This will automatically post the new data from the cell into the one below it. Just make sure you have no other formulas in the column. Also make sure the cell you target is the one where the form data is stored.