This was such a helpful set of videos - thank you! I used to rely on a bunch of CountIfs in order to have this sort of interactivity, which takes me hours! But your method (creating a copy of the data set with the query function that's dependent on the dashboard drop downs) will be my go-to going forward! This is genius! For those viewers out there who are already pretty familiar with these functions, the key points for me were in Part 2 around the 14 min mark and then part 3 around the 3 min mark.
Simply great, step by step but had to pause and rewind, hope the example sheets can be shared as hands on experience studies on the formulae, thank you.
Hi man, really appreciated your kindness to make those invaluable tutorials for community. It really helps me a lot! When I first saw your video, it took me almost 5 hours to get out of it! So addictive!
Man this is really awesome. I was searching throughout the net for this query since few months, to create a dynamic report at my work place. Today it tried an it came out wonderfully. Thank you very much. Keep up the good work. 😊👏👏👌👍
Thanks for this video mate! This was very helpful. I immediately experimented on 6 different charts and added 5 slicers for order date, sales area, sales rep, sales channel, and items. =)
meeeen i want to say i love you but am straight, but all your videos are just amazing another free leaning masters degree. Good good work i really like this
Outstanding tutorials -- I owe you. One place I was tripped up: if I filter a pivot table to exclude blanks then any new values are not automatically included. Example: if my 'Animals' column includes 'cats', 'dogs' and 'ferrets' and some blank rows, if I exclude blanks then all my animals are in the pivot. However, if I now add a row that includes 'birds' it is not included in the pivot unless I manually check it in the Pivot Filter. In effect, deselecting blanks turns the pivot filter into a manual decision what to include.
Hi can somebody help me, mine has an error and it says "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [2020-11-23)]. Date literals should be of form yyyy-MM-dd"
Would it be possible to build a similar dashboard where users could take advantage of the custom filters option? That way 2 users can look at different dates or regions concurrently
I want to ask a bit. If I use the formula above, I can only select one of the two cases for column C, either all or one of the values Midwestern, Northeastern, right? In case I want to select more than two values but not all values, what should I do? Please answer me, I really need it for my upcoming report
Awesome tutorial videos, I've been learning google sheets with the help of your channel for the past 2 weeks. I got a question, is it possible to make the pivot table and the chart dynamic while adding data in Transactions?
This is so nice, thank you a lot. BUT one question, if I need to filter with more fields. Say I want to filter both Region and Brand, how would the query look like? I tried to combine with more "where" and "and" but without success...
how can I filter it to be the month for quarterly reports rather than yearly report in the start and end dates? Also- one of the pivot tables are not updating.
I copied and replaced the query formula in 15:56 but it shows an error, I cross-checked it several times with the video and everything seems identical, yet it displays an error :((
@@ExcelGoogleSheets "Select A,B,C,D,E,F,G, Day(A)" & IF(COUNTBLANK('Helper Formulas'!A1:A3)=3,""," Where" & TEXTJOIN(" and ",True,'Helper Formulas'!A1:A3)) This one
I've gone through this series 2x now, and cannot get my date query to work? The query works fine as long I have no dates entered, but whenever I put dates in on my dashboard, my TempDataSet shows a #VALUE! error. Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "A "" at line 1, column 49. Was expecting one of: "(" ... "(" I don't even have 49 columns? I've tried all different formats for the column with my dates on the Transactions sheet. Any suggestions would be much appreciated! 😃
Hello, how can i format these tables so they alternate colours ? One line one colour, the other line another colour(but only on the table, not everywhere).. Or how to make it keep their text format whenever I add a new data or change the filters above ? Every time I add some new data or change the current item on the dropdown list, the table changes, but the format doesnt adjust.. I hope u got my doubt, sorry for bad english ;s
Rather than have the 2nd criteria be the date, how would I change it from being the date to another list? I've got everything to work with the date but I need it to be based on different criteria from the data
Love your videos they have helped out so much! I have having one issue, The Region section which is Tech for me works fine when my dates are blank. But whenever i choose a date no data is showing and i have a range which should show all my data. Hoping you could possibly help as here is the code i have in right now in the QUERY - =QUERY(Tabsheet!A4:G,"SELECT A, B, C, D, E, F, G, YEAR(A)" & if(COUNTBLANK(HelperFormulas!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,HelperFormulas!A1:A3)),1). once again love the videos!
@@yizhen001 Hi it's been a while since the first person posted this so I'll reply to you instead. I had the same error, make sure on the helper formula you have the correct Row for the date (My date is on row D and the tutorial is on row A when I first tested it, it worked because I changed the A to D but I forgot to do it on the helper formula one)
2.53 I stuck here and saying Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "F "" at line 1, column 83. Was expecting one of: "(" ... "(" ... Can you please help me here
This has been truly helpful but I can't seem to get past the 16:19 mark because I keep getting a Value error. This is what I have: =QUERY('Revised Data'!A1:U,"select * WHERE S = 'US' OR S = 'CAN'" & if(countblank('Helper Formula'!A1:A3)=3,""," WHERE " & textjoin(" AND ",TRUE,'Helper Formula'!A1:A3)),1) I even tried this one: QUERY('Revised Data'!A1:U,"select *'" & if(countblank('Helper Formula'!A1:A3)=3,""," WHERE " & textjoin(" AND ",TRUE,'Helper Formula'!A1:A3)),1) The different formula works fine on the Dashboard when I followed the video but when I went to put it into the data set, it gave me an error. Any help is greatly appreciated!
=QUERY('Master '!A1:G14,"SELECT A, B, C, D, E, F, G, YEAR(A)" & if(COUNTBLANK(helper!A1:A4)=4,"","WHERE " & TEXTJOIN(" AND ",TRUE,helper!A1:A4)),1) i have issue with count 4 : 1. start date 2. end date 3. region 4. country in above formula i have set count 4 but error is coming and i am not able to resolve this issue (if i take 3 count it's working but if i take 4 or 5 count it's not working can you resolve this issue )
Hello thanks for every lessons of helpful, understable, great experiences and interesting all. I need to know just 1 formula can you guys please help me resolve my problem. If same result same day how to count 1 in formula ?
Hi, Thank you for your videos, its really help me and it really works. Yet, i wondering if user can choose multiple, let say I want to see Midwestern, and Northwestern to see comparison between other religion. I hope you can make another video to how to make dashboard with multiple choice . Really appreciate Thank you
Thank you, great tutorial. The problem that I have is that an entire team uses the same Dashboard, so we have multiple users trying to interact with the dropdown for Region and Dates at the same time, would it be possible to somehow allow each individual user to interact with it independently? Thanks,
I'm trying to substitute the below query where date clause with your =if(COUNTBLANK(H15:H16)=2,""," WHERE " & TEXTJOIN(" AND ",TRUE, H15:H16)) but I'm receiving and error. Is it possibly to get it to work with a select like below, that has a group by and an order by? It works perfectly with select * Select J, count(O), avg(O), sum(O) WHERE J >= date"""&text(E2,"yyyy-MM-dd")&""" AND J
Hi, if you were building the formula without the data ranges, what would it look like? I have something built but getting hung up on getting the query to do nothing when "All" is selected.
Thx this helps a lot - I repeat it on my own file and works fine. BUT in the DASHBOARD tab add-in ColE the Year as an option to select from and add the formula for it in "Helpfromula" tab in A4. In my data tab the year is in ColE with the formula "=year(A2)" to get the year from the date. BUT I can get the formula in A4 (for the year) in "HelpFormula" tab correct . Can you please assist me with that formula, please?
Thanks, I did get it to work at last - the formula in my "HelpFormula" sheet A4 was not correct and now it is working and the formula is: =IF(ISBLANK(Dashboard!E2),"", "E = " & Dashboard!E2) I also add the months in the Datavalidationlist tab to make the validation not from the data tab . Thx
This is very Helpful, only issue i currently face is that 'All' works fine, however when i select the person on the drop down, then no data emerges, any idea's?
=IF(Dashboard!A3="all" , "", "C = ' ' " &Dashboard!A3& "'") =QUERY('re-attend data '!A1:F, "SELECT A, B, C, D, E, F" & if(COUNTBLANK('HELPER TAB'!A2:A4)=3,""," WHERE " & TEXTJOIN( " AND ",TRUE,'HELPER TAB'!A2:A4))) Thats what i have - C = ' '" ---- should a name go there?
Hi getting a "Value error" at the point where I tried replacing the formular on the TempDataSheet with the string. Error reads "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: S"
Thanks for the posting .. Really appreciate your knowledge and lucid presentation. After going through three parts i prepared the following string. My issue is this string works only when I leave start date and end date blank.. The moment i enter date the data vanishes from the dash board. =QUERY(Form!B1:I,"SELECT B,C,D,E,F,G,H,I,YEAR(B) " & if(COUNTBLANK (Sheet4!A1,Sheet4!A2)=2,"","WHERE " &TEXTJOIN("AND ",TRUE,Sheet4!A1:A2)),1) Note: I only need to filter the data according to start and end date, The Sheet 4 in the formula is your helperformula sheet in the video. Please help.
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!!
hi, thanks a lot for how to create dynamic string but when I select any date this error occurs "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [2019-08-15 ]. Date literals should be of form yyyy-MM-dd." whats wrong can you help?
Probably you are not a USA user, so the date format is different. You can create a new “date cell” in another place, like a hidden sheet, and format that "new" cell as USA date format: yyyy-mm-dd. Then, bring the content of the first cell (in your country date format) to that. So, refer your string to that USA date format cell. Worked fine here. Let me know if it works for you too.
Excellent! I just a have question, isn't changing query dynamically slow, can't we use conditional filters (referring to the same cells) to make it fast. considering if we are importing the data from other sheets.
Weird, my start and end date is in the correct date format but the query is unable to read my date, and mentioned that the date should be yyyy-MM-dd, but it already is! What is the issue here
@@ExcelGoogleSheets I found out the real issue, after testing, i realized my quote sign has a space before the date which affects the formula as well.... it should not have any space....thanks alot for your vids btw
Thanks a lot. Wonderful tuts. I have an issue as i try building my dashboard. I am getting this error and i dont seem to see where the issue is in the formular: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "A "" at line 1, column 32. Was expecting one of: "(" ... "(" ... Any help please?
This is the formular: =QUERY(Transactions!A1:Q,"SELECT A,E,L,M,N,year(E)" & if(COUNTBLANK(Helperformulas!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Helperformulas!A1:A3)),1)
Thanks for the video and hinting to use temporary data set to work with. I'm actually not well experienced in Excel and Google Spreadsheets, but I got the idea very quickly. Andthat is the counter part of the video: as for me I felt you over-explained most of the things, so I had to fast-forward the video lots of times :) But thanks anyway :)
As everybody, I'll first say that you are excellent in teaching. Thanks a lot. But, as a non American - I'm French, sorry... - I shall add 2 things. The first one is your voice. Your accent is perfectly and easily understandable -where some Texans are really a nightmare for me. The second one is the syntax of the Query statements, linked to what is generally called the "Locale". The "Locale" is the repository where you store the settings for the date and numeric formats, the currency and other stuff, linked to the country. And it happens that, in French, in the Query statements, the comma , is frequently turned into a semi-comma ; Which can be really confusing - the example shown in the tutorial does not work. This needs to be addressed with a very step by step approach. The kind of approach you have.
It was grateful video..However it throw the Error for the below query =QUERY(GrandTotalByDayByAgent!A3:F,"SELECT A, B, C, D, E, F,F*0.02 " & if(COUNTBLANK(Tips!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Tips!A1:A3))",1)")
No sure where I am making the mistake. Also tied with the following =QUERY(GrandTotalByDayByAgent!A3:F,="SELECT A, B, C, D, E, F,F*0.02 " & if(COUNTBLANK(Tips!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Tips!A1:A3)),1)
This was such a helpful set of videos - thank you! I used to rely on a bunch of CountIfs in order to have this sort of interactivity, which takes me hours! But your method (creating a copy of the data set with the query function that's dependent on the dashboard drop downs) will be my go-to going forward! This is genius! For those viewers out there who are already pretty familiar with these functions, the key points for me were in Part 2 around the 14 min mark and then part 3 around the 3 min mark.
Simply great, step by step but had to pause and rewind, hope the example sheets can be shared as hands on experience studies on the formulae, thank you.
Hi man, really appreciated your kindness to make those invaluable tutorials for community. It really helps me a lot! When I first saw your video, it took me almost 5 hours to get out of it! So addictive!
Good job. You are the #1 to explain google sheets.
This is like magic 😍. I think I have to re-watch from Part 1 to Part 3 again.
A very good teacher and an expert. Thank you brother.
Very good explanations and your talking is so clear that i was able to understand even with my poor level of english. Thanks...
Man this is really awesome. I was searching throughout the net for this query since few months, to create a dynamic report at my work place. Today it tried an it came out wonderfully.
Thank you very much. Keep up the good work. 😊👏👏👌👍
Thank you very much for the tutorial, I wish you all the best and be proud on your work!
MAN! that is so next level.... Awesome stuff, thanks for sharing your knowledge!
Amazing! Very useful to me as a beginner in making a dashboard in Google Sheet. Thank you all the best!
Just want to sau thankyou... You are my teacher..
Job well done! I finally completed this after two-thinking-days.
Thanks for this video mate! This was very helpful. I immediately experimented on 6 different charts and added 5 slicers for order date, sales area, sales rep, sales channel, and items. =)
meeeen i want to say i love you but am straight, but all your videos are just amazing another free leaning masters degree. Good good work i really like this
Really helpful! Excited to experiment with this for a few different sheets concepts. Thank you!
Outstanding tutorials -- I owe you.
One place I was tripped up: if I filter a pivot table to exclude blanks then any new values are not automatically included.
Example: if my 'Animals' column includes 'cats', 'dogs' and 'ferrets' and some blank rows, if I exclude blanks then all my animals are in the pivot. However, if I now add a row that includes 'birds' it is not included in the pivot unless I manually check it in the Pivot Filter.
In effect, deselecting blanks turns the pivot filter into a manual decision what to include.
Check out my Slicer videos for a solution for this problem.
Thank you so much! Your video helped me a lot today! 😉
You are just the best man!
Thanks!
Hi can somebody help me, mine has an error and it says "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [2020-11-23)]. Date literals should be of form yyyy-MM-dd"
Would it be possible to build a similar dashboard where users could take advantage of the custom filters option? That way 2 users can look at different dates or regions concurrently
Excellent tutorials. Good work man, very detailed one.
Very helpful, thanks a lot! Next thing for me is to go through this again, working with my own data. Learning by doing.
Great content, very useful, thank you!
Oh Man, Thank you so much for videos, you are a CRACK!
👍
I want to ask a bit. If I use the formula above, I can only select one of the two cases for column C, either all or one of the values Midwestern, Northeastern, right? In case I want to select more than two values but not all values, what should I do? Please answer me, I really need it for my upcoming report
Awesome tutorial videos, I've been learning google sheets with the help of your channel for the past 2 weeks. I got a question, is it possible to make the pivot table and the chart dynamic while adding data in Transactions?
You have a great talent and power to teach others. Keep the Good work going Boss.
this is awesome dashboard . This is very helpful for me
thanks a lot . But i want to ask you one thing how can i add
more count for my other use ?
Man you are really great doing this all..... Keep it up 👍👏👏👏
This is so nice, thank you a lot. BUT one question, if I need to filter with more fields. Say I want to filter both Region and Brand, how would the query look like? I tried to combine with more "where" and "and" but without success...
You are AMAZING at this. Thank you so much!
can we use checklist for the filter?? i mean to select more than 1 value
Thank you very much. Very help full
Keep up the good work🤝
is there any possible to dashboard filter without importing the data to the sheet?
I need to perform this for 3 sheets in one place
Thank you is very helpfull
What formula to use if we need to get the information by filtering Region
how can I filter it to be the month for quarterly reports rather than yearly report in the start and end dates?
Also- one of the pivot tables are not updating.
Very helpful! Thanks
I copied and replaced the query formula in 15:56 but it shows an error, I cross-checked it several times with the video and everything seems identical, yet it displays an error :((
What's your formula Alex?
@@ExcelGoogleSheets "Select A,B,C,D,E,F,G, Day(A)" & IF(COUNTBLANK('Helper Formulas'!A1:A3)=3,""," Where" & TEXTJOIN(" and ",True,'Helper Formulas'!A1:A3)) This one
@@alexrao8671 This last part doesn't seem to make sense TEXTJOIN(" and ",True,'Helper Formulas'!A1:A3))
go over that part again.
I've gone through this series 2x now, and cannot get my date query to work? The query works fine as long I have no dates entered, but whenever I put dates in on my dashboard, my TempDataSet shows a #VALUE! error. Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "A "" at line 1, column 49. Was expecting one of: "(" ... "(" I don't even have 49 columns? I've tried all different formats for the column with my dates on the Transactions sheet. Any suggestions would be much appreciated! 😃
same problem
Same problem! I'll be digging through the comments for a solution.
Hello, how can i format these tables so they alternate colours ? One line one colour, the other line another colour(but only on the table, not everywhere).. Or how to make it keep their text format whenever I add a new data or change the filters above ? Every time I add some new data or change the current item on the dropdown list, the table changes, but the format doesnt adjust.. I hope u got my doubt, sorry for bad english ;s
Rather than have the 2nd criteria be the date, how would I change it from being the date to another list? I've got everything to work with the date but I need it to be based on different criteria from the data
are you able to send me the google sheet so i can use it as example?
Amazing!! Thank you very much!
Love your videos they have helped out so much! I have having one issue, The Region section which is Tech for me works fine when my dates are blank. But whenever i choose a date no data is showing and i have a range which should show all my data. Hoping you could possibly help as here is the code i have in right now in the QUERY - =QUERY(Tabsheet!A4:G,"SELECT A, B, C, D, E, F, G, YEAR(A)" & if(COUNTBLANK(HelperFormulas!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,HelperFormulas!A1:A3)),1). once again love the videos!
I met the same error, is it fixed for you? I cannot find the error :(
@@yizhen001 Hi it's been a while since the first person posted this so I'll reply to you instead. I had the same error, make sure on the helper formula you have the correct Row for the date (My date is on row D and the tutorial is on row A when I first tested it, it worked because I changed the A to D but I forgot to do it on the helper formula one)
2.53 I stuck here and saying Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "F "" at line 1, column 83. Was expecting one of: "(" ... "(" ...
Can you please help me here
string is not working can you please help me out
Can you make it filter Multiple regions at once? I am trying to filter by product and want to choose more than 1 product.
This has been truly helpful but I can't seem to get past the 16:19 mark because I keep getting a Value error.
This is what I have:
=QUERY('Revised Data'!A1:U,"select * WHERE S = 'US' OR S = 'CAN'" & if(countblank('Helper Formula'!A1:A3)=3,""," WHERE " & textjoin(" AND ",TRUE,'Helper Formula'!A1:A3)),1)
I even tried this one: QUERY('Revised Data'!A1:U,"select *'" & if(countblank('Helper Formula'!A1:A3)=3,""," WHERE " & textjoin(" AND ",TRUE,'Helper Formula'!A1:A3)),1)
The different formula works fine on the Dashboard when I followed the video but when I went to put it into the data set, it gave me an error. Any help is greatly appreciated!
=QUERY('Master '!A1:G14,"SELECT A, B, C, D, E, F, G, YEAR(A)" & if(COUNTBLANK(helper!A1:A4)=4,"","WHERE " & TEXTJOIN(" AND ",TRUE,helper!A1:A4)),1)
i have issue with count 4 : 1. start date 2. end date 3. region 4. country
in above formula i have set count 4 but error is coming and i am not able to resolve this issue
(if i take 3 count it's working but if i take 4 or 5 count it's not working can you resolve this issue )
Hello thanks for every lessons of helpful, understable, great experiences and interesting all.
I need to know just 1 formula can you guys please help me resolve my problem.
If same result same day how to count 1 in formula ?
Hi, Thank you for your videos, its really help me and it really works. Yet, i wondering if user can choose multiple, let say I want to see Midwestern, and Northwestern to see comparison between other religion. I hope you can make another video to how to make dashboard with multiple choice . Really appreciate
Thank you
Harika .. çok güzel anlatım. Teşekkürler...
Thank you, great tutorial. The problem that I have is that an entire team uses the same Dashboard, so we have multiple users trying to interact with the dropdown for Region and Dates at the same time, would it be possible to somehow allow each individual user to interact with it independently? Thanks,
Not sure what to say. Did you try Google Data Studio?
You could create separate dashboard sheets for each individual. Perfectly identical just with different names.
Couldn’t they just use different filter views?
I'm trying to substitute the below query where date clause with your =if(COUNTBLANK(H15:H16)=2,""," WHERE " & TEXTJOIN(" AND ",TRUE, H15:H16)) but I'm receiving and error. Is it possibly to get it to work with a select like below, that has a group by and an order by? It works perfectly with select *
Select J, count(O), avg(O), sum(O) WHERE J >= date"""&text(E2,"yyyy-MM-dd")&""" AND J
or you can use add on 'Dicers', right ? :)
Hi, if you were building the formula without the data ranges, what would it look like? I have something built but getting hung up on getting the query to do nothing when "All" is selected.
Hard to say without looking at your particular case. You could share a spreadsheet with your setup.
I keep getting this error: "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN:" ANy suggestions?
Me too
@@jennababe7777 me too
Thx this helps a lot - I repeat it on my own file and works fine. BUT in the DASHBOARD tab add-in ColE the Year as an option to select from and add the formula for it in "Helpfromula" tab in A4. In my data tab the year is in ColE with the formula "=year(A2)" to get the year from the date. BUT I can get the formula in A4 (for the year) in "HelpFormula" tab correct . Can you please assist me with that formula, please?
Thanks, I did get it to work at last - the formula in my "HelpFormula" sheet A4 was not correct and now it is working and the formula is:
=IF(ISBLANK(Dashboard!E2),"", "E = " & Dashboard!E2)
I also add the months in the Datavalidationlist tab to make the validation not from the data tab .
Thx
How to do things like this with numeric variables. I tried to do exactly as the video but it wouldn't work
Its really helpful
This is very Helpful, only issue i currently face is that 'All' works fine, however when i select the person on the drop down, then no data emerges, any idea's?
Possibly missing single quotes around the name?
=IF(Dashboard!A3="all" , "", "C = ' ' " &Dashboard!A3& "'")
=QUERY('re-attend data '!A1:F, "SELECT A, B, C, D, E, F" & if(COUNTBLANK('HELPER TAB'!A2:A4)=3,""," WHERE " & TEXTJOIN( " AND ",TRUE,'HELPER TAB'!A2:A4)))
Thats what i have - C = ' '" ---- should a name go there?
Hi getting a "Value error" at the point where I tried replacing the formular on the TempDataSheet with the string. Error reads "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: S"
double-check your formula, as you might've copied any unnecessary signs such as = etc
Thanks for the posting .. Really appreciate your knowledge and lucid presentation. After going through three parts i prepared the following string. My issue is this string works only when I leave start date and end date blank.. The moment i enter date the data vanishes from the dash board.
=QUERY(Form!B1:I,"SELECT B,C,D,E,F,G,H,I,YEAR(B) " & if(COUNTBLANK (Sheet4!A1,Sheet4!A2)=2,"","WHERE " &TEXTJOIN("AND ",TRUE,Sheet4!A1:A2)),1)
Note: I only need to filter the data according to start and end date, The Sheet 4 in the formula is your helperformula sheet in the video.
Please help.
same problem I have...
Okay, you have to change the "", the other way around, it switched up when copied to "HelperFormulas". That will do it ;-)
Hey man, first thanks for creating these videos, but i have a question: How do I create the filters when only using text and not using dates?
ruclips.net/video/nLW8SerwnJo/видео.html
@@ExcelGoogleSheets That kinda worked, but how can i put it on the Dashboard?
I managed to make it, thanks.
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!!
hi can u please send me the google sheet?
hi,
thanks a lot for how to create dynamic string but when I select any date this error occurs "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [2019-08-15 ]. Date literals should be of form yyyy-MM-dd."
whats wrong can you help?
Probably you are not a USA user, so the date format is different.
You can create a new “date cell” in another place, like a hidden sheet, and format that "new" cell as USA date format: yyyy-mm-dd. Then, bring the content of the first cell (in your country date format) to that. So, refer your string to that USA date format cell. Worked fine here. Let me know if it works for you too.
Excellent! I just a have question, isn't changing query dynamically slow, can't we use conditional filters (referring to the same cells) to make it fast. considering if we are importing the data from other sheets.
I've learned a lot however I can't seem to filter the date. Any tips?
Make sure the dates are proper dates and the column is formatted as date.
Weird, my start and end date is in the correct date format but the query is unable to read my date, and mentioned that the date should be yyyy-MM-dd, but it already is! What is the issue here
Seems like you forgot to use TEXT function around your dates.
@@ExcelGoogleSheets I found out the real issue, after testing, i realized my quote sign has a space before the date which affects the formula as well.... it should not have any space....thanks alot for your vids btw
@@BF_official_my thanks brother it helped me solve my issue
@@ArINxCla wow bro, it's been 4 years since this comment glad it helps hahaha
Thanks a lot. Wonderful tuts.
I have an issue as i try building my dashboard. I am getting this error and i dont seem to see where the issue is in the formular:
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "A "" at line 1, column 32. Was expecting one of: "(" ... "(" ...
Any help please?
This is the formular:
=QUERY(Transactions!A1:Q,"SELECT A,E,L,M,N,year(E)" & if(COUNTBLANK(Helperformulas!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Helperformulas!A1:A3)),1)
doesn't DATE TEXT together cancel each other? why not just go with A>= '" & C2 & "'?
*good tutorial*
Thanks for the video and hinting to use temporary data set to work with. I'm actually not well experienced in Excel and Google Spreadsheets, but I got the idea very quickly. Andthat is the counter part of the video: as for me I felt you over-explained most of the things, so I had to fast-forward the video lots of times :) But thanks anyway :)
Countblank is not working on those formulas
Would you be able to send me the google sheet; so I use it while creating my own dashboard?
GRACIAS POR TU AYUDA
As everybody, I'll first say that you are excellent in teaching.
Thanks a lot.
But, as a non American - I'm French, sorry... - I shall add 2 things.
The first one is your voice.
Your accent is perfectly and easily understandable -where some Texans are really a nightmare for me.
The second one is the syntax of the Query statements, linked to what is generally called the "Locale".
The "Locale" is the repository where you store the settings for the date and numeric formats, the currency and other stuff, linked to the country.
And it happens that, in French, in the Query statements, the comma , is frequently turned into a semi-comma ;
Which can be really confusing - the example shown in the tutorial does not work.
This needs to be addressed with a very step by step approach.
The kind of approach you have.
I want to hire you
Vera mari
He used the formula
A. Text join
B. Query
C.if
D.is blank
Nice!
It was grateful video..However it throw the Error for the below query =QUERY(GrandTotalByDayByAgent!A3:F,"SELECT A, B, C, D, E, F,F*0.02 " & if(COUNTBLANK(Tips!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Tips!A1:A3))",1)")
No sure where I am making the mistake. Also tied with the following
=QUERY(GrandTotalByDayByAgent!A3:F,="SELECT A, B, C, D, E, F,F*0.02 " & if(COUNTBLANK(Tips!A1:A3)=3,""," WHERE " & TEXTJOIN(" AND ",TRUE,Tips!A1:A3)),1)