CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
snowblind
snowblind

Reputation: 81

Google sheet Query function not returning data from another tab?

I'm new to googlesheets but I have been using it for the last few weeks to dashboard some data I'm collecting. So far, so good, until now.

Background for context. I have around 2 or 3 separate google sheet docs which are populated when people complete google forms. They complete the form and I capture around 4 fields which are:

Address, Date In, Date Out, Guests

These sheets work perfectly. Each time a form is completed, another row is added with the new data. All good.

I then have a central google sheet which is where I am pulling all this data together.

I have sheet1, sheet2, sheet3 etc. All of which have the same columns. (Address, Date In, Date out, Guests).

The entire contents of each of these sheets are populated with an IMPORTRANGE formula in A1.

That means that in each A1 cell in each sheet, I am doing an IMPORTRANGE ("url", "sheetname!A1:D" and this works perfect. My central google sheet contains all the responses from these separate google sheets.

Here is my problem.

I want to join all of these responses into a single sheet (lets call it sheet4)

In sheet4 I simply want to pull the contents of A:D of the other 3 sheets and list them as a single list. To do this, I do the following:

=Query({'sheet1'!A1:D;'sheet2'!A2:D;'sheet3'!A2:D},"select * where Col1 is not null ",1)

(Note - I start at A1 in sheet1 to get the headers, then A2 from the other sheets as I only need the headers once.)

This pulls in all 4 columns except for sheet3 where it only pulls in the first columns data. It pulls in:

"Address" But it leaves the cells for Date in and Date out blank

I am expecting to see:

Address Date In Date Out Guests
1 Road 01/01 02/01 1
2 Road 02/01 03/01 1
3 Road 01/01 02/01 1
4 Road 02/01 03/01 1
5 Road 01/01 02/01 1
6 Road 01/02 02/01 1
7 Road 01/01 02/01 1
8 Road 01/01 02/01 1
9 Road 01/01 02/01 1

But I am seeing (lets say 6-9 Road are from sheet 3):

Address Date In Date Out Guests
1 Road 01/01 02/01 1
2 Road 02/01 03/01 1
3 Road 01/01 02/01 1
4 Road 02/01 03/01 1
5 Road 01/01 02/01 1
6 Road
7 Road
8 Road
9 Road

I just cant figure out why it wont pick up the data from cells B:D in sheet3 when the format is exactly the same in every sheet and there is data in the cells it should be seeing.

Thanks (sorry for the long post)

Upvotes: 0

Views: 1036

Answers (1)

player0
player0

Reputation: 1

most likely formatting issue. try:

=INDEX(QUERY(TO_TEXT({sheet1!A1:D; sheet2!A2:D; sheet3:A2:D}), 
 "where Col1 is not null", 1))

or use:

=FILTER({sheet1!A:D; sheet2!A2:D; sheet3!A2:D}, 
        {sheet1!A:A; sheet2!A2:A; sheet3!A2:A}<>"")

Upvotes: 3

Related Questions