This is exactly the overview I have been looking for. Thank you for explaining the starter basics so clearly!

@learnspreadsheets

Жыл бұрын

That’s so great you like it! Thanks!

I've watched tons of tutorials about Query Function, but your tutorial is the simplest way to follow. Thank you. I'm a new subscriber now. More to come!

@learnspreadsheets

3 жыл бұрын

Yay! Glad you found it useful, it’s a super complex tool so this video took me a while to pull together!

Probably the most useful guide for Google Sheets Queries I have come across! Great job! Definitely going to save this to reference later on 😁

@learnspreadsheets

2 жыл бұрын

This is so lovely to read! Thanks so much for this note

Thanks so much for the tutorial!!!

Your tutorial was really educational. Thnx for all the effort you put in this lesson.

@learnspreadsheets

3 жыл бұрын

No worries! Glad you like it. It’s a lot! As you say

fantastic video, content was clearly explained in a swift manner, not wasting a single second of viewers time. really informative and helpful, thank you and may god bless you with happiness and joy.

@learnspreadsheets

2 жыл бұрын

That’s so kind! Thanks so much, I do what I can so glad you appreciate it

very solid vid ty!

Great ! Thank u so much

great stuff man

Super helpful video, thanks so much for making it!

@learnspreadsheets

2 жыл бұрын

No worries! Glad you find it useful, thanks for saying it. Took me a while to pull together!

Thank you for this fascinating demonstration.

@learnspreadsheets

3 ай бұрын

No worries! Glad you like it 😃

Amazing David, amazing. You codensed so much stuff in wachtable 18 minutes. Bravo!

@learnspreadsheets

3 жыл бұрын

Thanks! Glad you like it

@learnspreadsheets

3 жыл бұрын

Check out my g sheets dynamic array vid, I condensed even more stuff in that one!

@coldavenue2325

3 жыл бұрын

@@learnspreadsheets Hey David, could you make a video about how to make the =googlefinance function dynamic. In column C is my ticker symbol, in column D my price of the stock. I want the Col D to pull down dynamically whenever new tickers are added to col C. I found some solution with the help of a script. community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727 Secondly I am searching for a solution for importing .xls files from web directly to google sheets.

GREAT video! Thank you.

@learnspreadsheets

2 жыл бұрын

Glad you like it!

Thank you... It was long but very useful.

@learnspreadsheets

Жыл бұрын

Thanks, I’m glad you enjoyed it despite the length. There’s a lot to get through!

Great job. Thank for sharing. I suscribe

@learnspreadsheets

Жыл бұрын

No problem! Glad you found it useful!

Thanks!!!!

Thank you, it was interesting

@learnspreadsheets

Жыл бұрын

No problem, glad it’s useful!

Wonderful❤

@learnspreadsheets

3 ай бұрын

Glad you like it!

Thnx. A ton 👍

@learnspreadsheets

Жыл бұрын

Glad you like this one! I have another one on dynamic arrays in sheets which is also handy & one on how to make a query builder in sheets

very great tutorial, thx

@learnspreadsheets

2 жыл бұрын

Thanks! Glad you one it!

Bonsoir; MERCI pour ce tuto vidéo avec les bases de QUERY vraiment bien expliquées en si peu de temps. Du coup, je me suis abonnée à votre chaîne. Google Translat fr > UK Good evening; THANK YOU for this video tutorial with the basics of QUERY really well explained in such a short time. So I subscribed to your channel.

@learnspreadsheets

2 жыл бұрын

Très comptent que tu apprécie ça, ça m’a pris de temps pour faire le vidéo! Je parle français alors ça va 😃

Great full video my study & thanks

@learnspreadsheets

2 жыл бұрын

Glad you like it!

This is good video about query fu, 👍

@learnspreadsheets

8 ай бұрын

Glad you like it! Thanks!

Fantastic 😊

@learnspreadsheets

Жыл бұрын

Glad you like it!

Really very helpful video & nice explanation sir🇮🇳 Love from india 🇮🇳

@learnspreadsheets

3 жыл бұрын

Thanks for the feedback! I worked hard on that one 😃

Great tutorial learnt so much, you should have shared a google sheet with all various clauses and finally on the same workbook the dashboard you showed with query referring to data from cells.

@learnspreadsheets

3 жыл бұрын

Thanks for the feedback and the recommendation, I made this tutorial a while ago but recently I have started sharing workbooks for them

@greggswaim1088

2 жыл бұрын

@@learnspreadsheets Just happened across this video; so impressive. How can one access the workbooks? Thanks for the lessons so far!

@learnspreadsheets

2 жыл бұрын

Thanks Gregg, if you subscribe & email me to david@xlconsulting-asia.com I can share it

Great video. I'd love to see more on how you set up the sheet in Refer to cell/dropdown section. I'm attempting to build something similar but with an IF statement for All items in the dropdown. I can't get an Order by item to work with the IF statement.

@learnspreadsheets

3 жыл бұрын

Hey! Thanks for the feedback, I have a couple of other. Videos on drop down lists in g sheets. Google sheets rises above excel in a few areas, notably data validation and the QUERY function,

@stephenmarkagripo4288

2 жыл бұрын

pls make the video about it. i want that topic the timesheet.

Thanks Mr. David for a complete package in one go. please guide if we can format cell borders through query function.

@learnspreadsheets

Жыл бұрын

Hi sadly you cannot apply formats as they don’t pass through a function

I love learning excel!

@learnspreadsheets

3 жыл бұрын

Yay! Who doesn’t 😃

I love "Query" in Google Sheets and you explained it briliantly!! Actually I have a question: I want to return the label "week number" in my query but it returns double (in two columns) can you please help me what can be the reason? My query: =query(data, "select A, B, C, D, E, F, G, H, I, avg (I) where B is not null group by A, B, C, D, E, F, G, H, I order by B ASC label avg(I) 'week number'")

@learnspreadsheets

Жыл бұрын

Thanks! I’m glad you like it, sorry but I’m not sure unfortunately, week numbers are hard to deal with in general

Hi just wondering if there is a way to join data sets by a certain column? Many thanks

@learnspreadsheets

2 жыл бұрын

Nah it doesn’t allow for it unfortunately. Here are some ideas though… stackoverflow.com/questions/14796620/google-spreadsheet-query-join-equivalent-function

Loads of helpful information, clearly presented. Your edits at the end of sections are a bit (no, a LOT) sudden.

@learnspreadsheets

Жыл бұрын

Glad you like the video, & thanks for feedback on the edits, there was a lot to get through on this video so I opted to cut time at the end of sections but I’ll take feedback on board for future videos

Hi, thank you for posting. Could you clarify something please. I have watched this for cell reference text in query """&&""" but I see you have something different, both are valid?

@learnspreadsheets

Жыл бұрын

Hi I’m glad you liked it, what I use in my video works I can guarantee, other syntax is also valid for some instances

06:14 Yeah, but how can I sort them in that dropdown? (e.g. alphabetically, or numerically for numbers) 08:19 Is there any way to specify a default value shown on that dropdown instead of just a blank cell with a little arrow? 12:22 What if some of the columns returned from the query are empty? Is there any way to omit such empty columns from being returned by the query? (But only if they are actually empty in the RETURNED list of data; in the original data set, these columns might have contained something in some rows, but after filtering with "where", they got empty.)

@learnspreadsheets

2 жыл бұрын

Sorry but I would need to get more info to understand, there are many more detailed guides to QUERY that may be able to help

@erikscs3924

Жыл бұрын

​@@learnspreadsheets Hey man, how are u doing? Ik I'm 1 year too late, but in case you see this reply, could you plz help me out with this formula?? I had success on elaborating a formula that could import selected columns from another spreadsheet using IMPORTRANGE and QUERY. This is the formula: - =SORT(QUERY(IMPORTRANGE("type_url";"'Tab1'!A2:K200");"select Col3, Col7, Col2, Col10, Col8 where Col1 is not null order by Col3 asc"); 1; VERDADEIRO) - . But now I need to capitalize the first letter of each word from the col3, only the col3. I've tried to user PROPER function in various ways, and still nothing... Do you know if there's some way to do it keeping this formula?

the best summary video about Query Function I have one issue with this function wich is in "Combine sheets dynamically QUERY" at 12:20 when you added "Add" in the first data it showes up in the middel of combined data. Is there a way where it shows up at the end?

@learnspreadsheets

10 ай бұрын

Hello! Thanks for the kind words! You can rearrange the columns in QUERY using Select & then listing out the columns in order

@learnspreadsheets

10 ай бұрын

Check out my other video on making a query output which will give more insights into the process I use! kzread.info/dash/bejne/eIh_rLyCoqvMqrQ.htmlsi=XLaAsBHosCtgkpoj

At 8:02 you are explaining how to add the filters to your query. How did you set the string output in cell G10 as your variable in the query? You cut JUST before clicking on that cell

@learnspreadsheets

2 жыл бұрын

I set the criteria in a formula that is then linked to the cell. So one formula uses & etc to return the criteria I want to make work in query and then another formula is taking the query function with the full criteria

@StephenPiela

2 жыл бұрын

@@learnspreadsheets so it would essentially be =QUERY(range, &CHAR(42)&G10&CHAR (42))

@netschaap

2 жыл бұрын

@@learnspreadsheets [edited] I originally requested the formula in cell G10 since I couldn't manage it to work. However, this was due to another reason (namely: string search parameters must be put between single quotes). It works perfectly with a formula such as: =query(range;G10). Thanks for this very instructive video, I've been looking a long time for this functionality!

@learnspreadsheets

2 жыл бұрын

Nice one! Glad you could make it work 😃

é trazer junto o formato da célula possível? (por exemplo a cor de fundo da célula pai)

@learnspreadsheets

Жыл бұрын

Formatting cannot come with the cells via the Formula unfortunately

@williampedrodossantosbarbo6008

Жыл бұрын

@@learnspreadsheets thanks!!

3:38 How could the command that you used to include "Londo" also include the ones that had "London" they weren't in the command with it at all

@learnspreadsheets

Жыл бұрын

Hi good question. If you use CONTAINS or LIKE it will include anything which contains those characters regardless of whether there are other characters in the cell or not

Hi David. Thank you for the tutorial. It was very useful. Can you please help me with this. =QUERY(C:R,"select * where C = '"&A3&"' AND I = '"&A4&"' ") The above formula is working fine, but my actual requirement is I sometimes want to return data were data is filtered only for I and not for C. I don't want to use the IF function as there would be more than one AND operator in my final QUERY function. PS: I did read the comments trail to see if something similar has been asked before, but it returned blank. Looking forward to your reply.

@learnspreadsheets

Жыл бұрын

I'm glad you find it useful, this should help with what you are looking for: infoinspired.com/google-docs/spreadsheet/and-or-and-not-in-google-sheets-query/

Hello David, Outstanding tutorial. thank you very much. I am looking for some help trying to run a power query web connection in google sheet with IMPORTHTML but I am failing and need help. Can you assist? If you could drop me a note and let me know how I can get in touch.

@learnspreadsheets

2 жыл бұрын

Great to read this comment Frederick! Im glad you found it useful. Sorry but my experience with IMPORTHTML is quite limited, you can email me on david@xlconsulting-asia.com to see but I don't have too much experience as mentioned

I am from India

@learnspreadsheets

7 ай бұрын

Great! Glad you’re watching!

You say that with the FILTER function you're limited to the order of the columns in the tablw, but that is wrong. Other than that, good video

@learnspreadsheets

Ай бұрын

Thanks for the feedback, I probably didn’t explain it well - sorry, the FILTER function returns the same columns you selected originally. If you want certain columns to be hidden or reordered you need to add CHOOSECOLS or INDEX or another function with it

(PT-BR) Olá professor! Excelente conteúdo! Me ajudou muito! Consegui aplicar numa planilha, no entanto, surgiu uma dúvida. Como corrigir o "erro" #N/D, quando um dos critérios não for atendido? Estou utilizando esta fórmula, onde B1 refere-se à turma (por ex.: 6º ANO) e E1 refere-se à disciplina (por ex.: Matemática): =QUERY(CONSULTA_DB!B4:L;"select * where J Contains '"&B1&"' and F Contains '"&E1&"' ") No entanto, quando seleciono outra disciplina, que não possui registro nessa turma (por ex.: Artes), a célula onde está a função Query retorna a mensagem acima (#N/D) Muito obrigado!!

@learnspreadsheets

2 жыл бұрын

Thanks for the comments but I don’t speak Portuguese sorry

@regiskoepsell7794

2 жыл бұрын

@@learnspreadsheets Hello how are you? I used Google translator to re-report my question. ------- Hello teacher! Excellent content! Helped me a lot! I managed to apply it in a spreadsheet, however, a question arose. How to fix #N/A "error" when one of the criteria is not met? I'm using this formula, where B1 refers to the class (eg 6th grade) and E1 refers to the subject (eg Mathematics): =QUERY(QUERY_DB!B4:L;"select * where J Contains '"&B1&"' and F Contains '"&E1&"' ") However, when I select another subject, which has no record in that class (eg Arts), the cell where the Query function is located returns the message above (#N/A) Thank you very much!!

@learnspreadsheets

2 жыл бұрын

I would maybe wrap it inside an iferror to replace an error with 0 or a “” blank

@regiskoepsell7794

2 жыл бұрын

@@learnspreadsheets Good idea! Thanks for the feedback and the suggestion.

@learnspreadsheets

2 жыл бұрын

Yay no problem! Glad it helped 😃

Sir I need your help regarding Google Sheet

@learnspreadsheets

2 жыл бұрын

Hi ok I can offer some paid consulting if that’s what you need for sure!

I want to learn google sheet

@learnspreadsheets

3 жыл бұрын

Great idea! I love google sheets 😃, these dynamic arrays are brilliant

Doesn't work

@learnspreadsheets

Жыл бұрын

Sorry but my methods do work, it’s easy to make mistakes when writing the code & getting single & double quotations mixed up, I hope you can follow the tutorial & get it working

great stuff man

@learnspreadsheets

3 жыл бұрын

Yup! Love this function 😃