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:

Form Response Tab Google Sheets

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:

Cell-by-cell display of data from another Sheet Tab

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.

Google Sheets using cell-by-cell transfer into another sheet with live data does not work
GIF file may take a few seconds to load.

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:

Live data transfer to new tab error with cell-by-cell approach Google Sheets
‘Bad Use’ Sheet tab

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:

Live data transfer to new tab with array approach Google Sheets

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.

Google Sheet Live Form response tranfer Array vs Cell-by-cell
GIF file may take a few seconds to load.

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.

Want a solid step-by-step course to become a pro at Google Sheets? Udemy has some professional courses that will turn you into an admin ninja!

I’m a huge fan of Justin Mares, Mastering Google Sheets course.* 

 

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website. 

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

Leave a Reply