0

Objective 1: Trying to query data on another tab in Google Spreadsheet
Problem 1: Getting #N/A when I specify the range from the other sheet

I have read about specifying a range on a different tab and seen videos on youtube but I am not able to make that scenario work for me. I have also looked at several questions here including How to Query Function in Google Sheets (data filtering to pull data to other sheets) but have not been able to find a solution. In this image, you will see how data is laid out. As you can see, I'm able to make the query work if the range is on the same tab as the query. The second I change the range to a different tab, I start getting #N/A as you can see here. (You will note that the data on the different tab is located in a different range. If that matters, please let me know.)

Thanks in advance!

techscolasticus
  • 97
  • 13
  • From the first picture I notice some different formatting in col A. The 7's are formatted as number (right aligned) while the 8's are numbers formatted as text (left aligned). That in itself is already troublesome for query() as it is know to struggle with 'mixed data' like that. Please consider sharing an example spreadsheet so we can have a better look. – JPV Aug 31 '15 at 13:59
  • Hi JPV. Thanks for responding! [Ex sheet](https://docs.google.com/spreadsheets/d/1Un8lN_5IqFA_fXL8NpXY6DPFB8JiwW6ZjJUHGMLPYFU/edit?usp=sharing) I observed that myself but wasn't sure if it was related because when I formatted all the 7 as number, it was working. As soon as I change formatting for even one 8 in col A to number, it goes bonkers. I'm assuming that col B and col C being text isn't helping either? I can't format that data as plain text because GA spits out data in spreadsheet and I don't have control over what format it applies to new data. I was hoping to simply query the data. – techscolasticus Aug 31 '15 at 14:40

1 Answers1

1

Try this query:

=query('High Level Metrics by Device and Medium'!A2:D, "select C, sum(D) where A = '"&A2&"' group by C pivot B  ")

in case you have mixed data, try converting the first column to text:

=ArrayFormula(query({to_text('High Level Metrics by Device and Medium'!A2:A), 'High Level Metrics by Device and Medium'!B2:D}, "select Col3, sum(Col4) where Col1 = '"&A2&"' group by Col3 pivot Col2 "))

As an alternative to query() you can try:

=ArrayFormula(iferror(vlookup($A$2&B$2&{$A$3:$A$5}, {'High Level Metrics by Device and Medium'!$A$2:$A&'High Level Metrics by Device and Medium'!$B$2:$B&'High Level Metrics by Device and Medium'!$C2:$C, 'High Level Metrics by Device and Medium'!$D$2:$D}, 2, 0)))
  • the $A$2&B$2&{$A$3:$A$5} creates one string per row (A3:A5) out of the values A2, B2 and the range A3:A5, that looks like "7OrganicDesktop".
  • the vlookuprange is created by concatenating the columns A, B, C of the sheet 'High Level...' (so one 'new' column is created out of these three --> the data will look like the example above), and a second column is simply the range D2:D.
  • when a match is found the vlookup returns the D-column

This formula can be dragged to the right, for the other values. I hope that helps ?

JPV
  • 26,499
  • 4
  • 33
  • 48