My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?

Google Sheets – Intermediate, Arrays, Form Data

Picture this. You have set up your Google Sheet and attached your Google Form data to it so you might get a tab that looks a little something like this:

You probably don’t want to mess with this tab because the Form is still live.

Instead, you decide to create a new Google Sheet tab that you want to automatically transfer all the data into, including the current form response data and any new form responses you might get.

The Common Mistake

A common, though mistaken, approach to this is to do a cell-by-cell transfer of data. For example, we would grab the first data cell of our “Form responses 1” Sheet tab and in a new sheet tab cell we would write:

We would then drag that cell across to the right to cover all the columns. Then all the way down to the bottom of the page to cover the current responses and any new responses added.

That might look a little like this:

The problem

This looks like it might work, right? Let’s test it out by adding in a new form response. For me, it will be the 6th response and will appear on row 7 of the ‘Form  Responses 1’ sheet tab.

Do me a favour and go ahead and give it a try yourself:

You can take a look at the Google Sheet by accessing it here:

Demographic Form

You can see now that in the ‘Form responses 1’ sheet tab we have a new entry but in the ‘Bad Use’ tab, it doesn’t appear.

So what’s going on?!

If we go over and take a look at the ‘Bad Use’ Sheet, selecting View > Show Formulae from the menu and then look at rows 6 and 7 we can see a discrepancy:

As you can see, in row 6 of the ‘Bad Use’ tab, it is drawing the correct data from the ‘Form responses 1’ sheet. However, on the next row (row 7), it is now drawing from one space below the last entry of the ‘Form responses 1’ sheet. It is jumping from row 7 (our new response) to row 8. It is as if the whole row has been removed.

This will occur every time we add a new form response. So this means we can’t transfer data using a cell-by-cell formula.

So what can we do?

The Solution

Google Sheets Arrays to the rescue!

We can circumvent this behaviour by building an open-ended array of the form responses data.

Basically, in one cell we can create a formula to grab all the data in our selected columns and rows.

We’ll create a new Google Sheet tab called ‘Good Use’ and add all the header information into it.

To display an array of data from another sheet tab or the same sheet, we use A1 notation for our start and end values. We then place those two values into curly braces, like so:

For our example, our start cell will be ‘A2’. We don’t want and end row value but we do want to only bring across columns up to ‘G’. So we just leave out the row number and  simply add ‘G’, like so:

On the sheet it will look like this:

Using just this array formula approach in cell  ‘A2’  all the data in the selected range will be added to the ‘Good Use’ Sheet tab.

Now when a user enters a new form response we won’t lose the data on our ‘Good Use’ Google Sheet Tab.

Let’s add a 7th response to the tab and then go back and check both the ‘Bad Use’ and ‘Good Use’ tabs.

Conclusion

As you can see, using the Array approach to displaying the form data in another sheet tab transfers data across live. Now you can go ahead and apply filters, conditional formatting, extra columns, formulas, etc to your data without worry.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.