Oct 15, 2019
Can you pull data from MULTIPLE tabs and have it auto-populate a single tab? How do you do this?
I am currently using this formula:
- =query('TEA (main cabinet)'!A4:AB,"Select * Where B='DATA'")
- =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!
Details
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
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
All Replies (4)
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!
----
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
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.#VALUE!ErrorIn ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
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