Oct 15, 2019

Can you pull data from MULTIPLE tabs and have it auto-populate a single tab? How do you do this?

I have a spreadsheet (anyone with link can comment.  If you would like to edit, please copy and link back to your edited document!)

I am currently using this formula:

  • =query('TEA (main cabinet)'!A4:AB,"Select * Where B='DATA'")

I have two other tabs that I need to pull information from and the formulas would be this (provided they were stand-alone tabs):

  • =query('TEA CART'!A4:AB,"Select * Where B='DATA'")
  • =query('Adagio Sample Sets'!A4:AB,"Select * Where B='DATA'")

However, I need all three of these to merge into one query OR find another formula that will allow me to pull from three tabs to merge into one tab.

DATA refers to the tea types - each tea type needs to merge into it's own tab.  So:

  • Black teas from "Tea (main cabinet)", "Tea Cart", and "Adagio Sample Sets" tabs will all show up into the "Black" tea tab.
  • Shou teas from "Tea (main cabinet)" and "Tea Cart" tabs will all show up into the "Ripe/Shou Pu'er" tea tab.
  • etc etc etc



Finally, I have some tabs that have teas I would like to put in a misc category.  So "purple" tea, "hei cha", and "buds" would all obtain their own category.  Therefore, DATA (in the query) would need to be multiple values.  For example:

  • =query('TEA CART'!A4:AB,"Select * Where B='DATA 1, DATA 2, DATA 3'")


*Green Tea tab needs to have a combination of all of this.  Not only does green tea need to pull from "Tea (main cabinet)", "Tea Cart", and "Adagio Sample Sets" but it also needs to be able to pull both data sets in column B "green" and "matcha" from the  "Tea Cart" tab.

I feel SUPER out of depth here.  It's all pieces I know how to do individually but I feel like there has to be a way to combine it all!  

Thanks in advance for your help!
Locked
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Oct 15, 2019
Recommended Answer
Oct 15, 2019
You can create a custom or in-line array on which to run the QUERY(). That is {'TEA (main cabinent)'!A4:AB;'TEA CART'!A3:AB;'Adagio Sample Sets'!A4:AB}. You can use multiple optional conditions with OR between them like B = 'Data 1' OR B = 'Data 2'.
Therefore =QUERY({'TEA (main cabinent)'!A4:AB;'TEA CART'!A3:AB;'Adagio Sample Sets'!A4:AB},"Select * where Col2 = 'Data 1' OR Col2 = 'Data 2'")
 
When using any data set other than a standard sheet range you must use Col# notation rather than A1 notation.
Original Poster Smote marked this as an answer
Helpful?
All Replies (4)
Recommended Answer
Oct 15, 2019
You can create a custom or in-line array on which to run the QUERY(). That is {'TEA (main cabinent)'!A4:AB;'TEA CART'!A3:AB;'Adagio Sample Sets'!A4:AB}. You can use multiple optional conditions with OR between them like B = 'Data 1' OR B = 'Data 2'.
Therefore =QUERY({'TEA (main cabinent)'!A4:AB;'TEA CART'!A3:AB;'Adagio Sample Sets'!A4:AB},"Select * where Col2 = 'Data 1' OR Col2 = 'Data 2'")
 
When using any data set other than a standard sheet range you must use Col# notation rather than A1 notation.
Original Poster Smote marked this as an answer
Oct 16, 2019
EDIT:  WELL SNAP.  I didn't realize I was had ONE EXTRA COLUMN in the "Tea Cart" sheet - so I added an extra column to the other two sheets and now the formula works perfectly!  THANK YOU!

----

Hmmm, well it seems to partially work.  I spent the morning adjusting my sheet so that Tea Cart matched the formatting of Tea Main (shortened the name) and Adagio Sampler Sets.  *I literally duplicated the TEA MAIN sheet and copied over the values from the old TEA CART sheet.

However, I'm still coming up with an error.


In the black tea chart, 
=QUERY({'TEA MAIN'!A3:AB; 'Adagio Sample Sets'!A3:AB},"Select * where Col2 = 'black'") 
works perfectly fine and pulls from both TEA MAIN and ADAGIO SAMPLE SETS.

However, if I do:
=QUERY({'TEA MAIN'!A3:AB; 'TEA CART'!A3:AB; 'Adagio Sample Sets'!A3:AB},"Select * where Col2 = 'black'") 
I get 
#VALUE! 
Error 
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

It seems to be an issue with the TEA CART sheet specifically - I can use the other two with no issue but any combo of TEA CART with either of the other two causes the error.  

So this works great for getting the multiple values in the sheets where I want them but I'm still stuck on how to get the TEA CART sheet to function!
Last edited Oct 16, 2019
Oct 16, 2019
Awesome! Glad that worked out for you.
Oct 16, 2019
Well most everything seemed to work!  I started a new thread since this issue is entirely different than what the original problem was, but if you've got any advice, I'll take it!  https://support.google.com/docs/thread/16962188?msgid=16962188
false
15079195017741124228
true
Search Help Center
true
true
true
true
true
35
false
Search
Clear search
Close search
Main menu