Consolidate Data From Multiple Sheets 📝 With ONE FORMULA Using Google Sheets

Подписаться 2,2 тыс.

Просмотров 44 тыс.

50% 1

Learn how to bring the data from four tabs into one master sheet. We do all of this using the QUERY function.
See more here on QUERY here:
sheetshelp.com/query/
See the origin of this formula in the "Writing the query" section here:
sheetshelp.com/formulas-for-n...
#shorts #googlesheets #spreadsheet

Наука

Опубликовано:

6 сен 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 44

@KimaniWebb 8 месяцев назад

Great video! I appreciate that it was so concise and the visual of the formula bar helped so much.

@SheetsHelp 8 месяцев назад

I appreciate the feedback. I'll have to do that more often.

@user-ye3wm9zc8d 7 дней назад

Thank you!

@SheetsHelp 5 дней назад

Glad you liked it!

@bhaveshthaker2035 3 месяца назад

Amazing... 👍

@SheetsHelp 3 месяца назад

Thank you! Cheers!

@tadeoriganti 8 месяцев назад

Holy cow! Thanks a lot! ❤

@SheetsHelp 8 месяцев назад

Glad you like it!

@arusha1981ify Год назад

You're amazing

@SheetsHelp Год назад

Glad you like it!

@imotoshiz 5 месяцев назад

Hi- all spreadsheets had the same columns and headers name but can I do these where the 3 first columns of each tabs have the same headers but the other columns are not. Example tab 1,2,3 column A is country, b, state and c city but then each columns in each tab after column have headers with information and I want to combine all of this into one .

@SheetsHelp 5 месяцев назад

I wouldn’t recommend it. You’ll have a hard time getting meaningful output.

@cgbeautyink Год назад

Will this automatically update when cells are added or manipulated on other tabs?

@SheetsHelp Год назад

Yes, it shows a live version of the data.

@sumanmandal7023 Год назад

May i use Query function to merge 10 workbooks into one sheet ?

@SheetsHelp Год назад

Yeah, that would work.

@johnkennethechave6440 15 часов назад

Why it didn’t work on my google sheet? Is their anything to do so I can use it? I already did the instruction seen in the vid. Thanks for the helps

@SheetsHelp 7 часов назад

I can't tell without seeing your sheet.

@happilydecluttered 3 месяца назад

Can you do this with a range of sheets? I have new sheets added every time I have a new client and I want the new info to continually populate in my Summary sheet. THANKS!

@SheetsHelp 3 месяца назад

I don't think so. That would be something you have to do with Apps Script.

@happilydecluttered 3 месяца назад

@@SheetsHelp Thanks for letting me know!

@x-el.s 5 месяцев назад

Great video! thanks. one comment though-you could get the same result just using the curly brackets. the usage of query is applicable when you choose ranges like A1:E that doesn't have an end for future data. and thus using the formula.... =QUERY({....;.....;.....}, "SELECT * WHERE Col1 IS NOT NULL")

@SheetsHelp 5 месяцев назад

Thanks for the alternative. I am not clear on why the change though and referencing the curly braces. Both formulas are using curly braces.

@x-el.s 5 месяцев назад

in your example you could only use the curly brackets only@@SheetsHelp

@howieburd 10 месяцев назад

How would you use this formula if one table has more column headers and the other table is missing a few headers from the main table. I have two tables, one of which is a condensed version of the other. I want to be able to add the condensed version to the main version (match the similar headers)

@SheetsHelp 10 месяцев назад

It's kind of a lame answer, but honestly, it's impossible to say without seeing the Sheet. These formulas can be quite different based on the factors specific to your situation. Sounds like a trick problem though, let us know how you solve it!

@aliyakoy4197 9 месяцев назад

Results was not automatically expanded, please insert more rows

@SheetsHelp 9 месяцев назад

The QUERY function can't change the size of your spreadsheet so you'll have to manually add rows. Then, the function should be OK. Unless you have millions of rows, in which case Sheets may not be able to handle the size.

@aileenm1089 5 месяцев назад

I cant select the tab after typing the =Query({

@SheetsHelp 5 месяцев назад

You can always type them in. This video explains the different types of cell references. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-81KM2hnT9UQ.html

@ajie1922 6 месяцев назад

How if one sheet?

@SheetsHelp 6 месяцев назад

It would be a similar process, but use simple cell references like A1:D100 instead of the references to other sheets.

@saurabhjain9509 8 месяцев назад

Is there any way that names in coloumn will not be duplicated

@SheetsHelp 8 месяцев назад

You can prevent the headers from being duplicated by starting in the second row for each new worksheet (like in the video). If the duplicates are throughout the data, consider adding in the UNIQUE function.

@saurabhjain9509 8 месяцев назад

@@SheetsHelp Thanks alot

@rvsm3150 Год назад

Is it possible to use multiple sheets as reference for my drop down filters? When I try it, it won't filter the data I want. =QUERY({Data1!A1:G22;data2!A1:G22}, " SELECT * WHERE Col1 IS NOT NULL AND Col1 >=DATE """&TEXT(D5,"yyyy-MM-dd")&""" AND Col1

@SheetsHelp Год назад

I am not sure. What I would do is try the drop downs from different sheets without the QUERY and see if it works. Then I'd try the QUERY without the drop down and see if that works. Then try them together.

@rvsm3150 Год назад

@@SheetsHelp Thank you very much!!! I will go and try it out.

@x-el.s 5 месяцев назад

assuming you have two arrays in column K and column L, and you want to join them and limit between two dates that are in M9 and N9 you should try this syntax =QUERY({K12:K;L12:L},"SELECT Col1 WHERE Col1>=DATE"&TEXT(M9,"'yyyy-mm-dd'")&"AND Col1