Came here from the budget spreadsheet video, it has changed my life. Query is an amazing function, thank you for explaining it, especially the miscellaneous syntax like ”select”. Expanding my spreadsheet vocabulary every day!
That drives me crazy in the forms how new responses don't keep formatting or formulas. I learned the feature you showed a while back, but knowing that is how you suggest to do it too, gave me confidence knowing I was doing it the same way, so thank you. Learned a few more tips in this video too.
I wish all working professionals see this on their recommendations. This is just so good and timely. We started using Chromebooks because we’re working remotely and I know nothing about google sheet. Can we add new responses and pivot on a separate file rather than add new sheets so noone messes up those two sheets? I noticed that the google sheet is so laggy and it affects all of us working remotely because we share the same files. Any tips how to fix it? 3 months of data and the file is lagging.
would you please help me with this case ( imagine that I have two group of courses A and B and each one of them have 12 class per months , 3 class per week what is the formula that i need for example to choses ( course A ) will desplay ( saturday , Monday ,Wednesday) and if chose ( course B ) will desplay (Sunday ,Tuesday ,Thursday )
it would be great if you could show us to make a form that dumps into an expense tracker spreadsheet, and then how to make a budget dashboard from that. I've ALMOST got it, but where my expenses were negative, now my budget dashboard charts are upside down (-1,000 to zero). I'm thinking in my form I may make 3 number questions, expense, income, and total... or I'm really not sure how to make these graphs work off of my data yet. I will paste a link to the video I started with. My issue is that I want it to also track the balances in my accounts because it doesn't make sense to do all of this work and still have to keep a checkbook somewhere else. I hope that makes sense!
Thanks for the info. Just subscribed. Question - what if I want the query to filter out two columns instead of one? Example, what if I wanted only a particular shirt size AND the shirt color to be filtered in the query? How would that formula look (if possible)?
This is really helpful thank you. I am a teacher collecting data on students. Is there a way to write the Query so that the different sections realign on the new sheet? I can explain further if you have a chance. THANK YOU!
Yes there is. You can align columns in any order. For instance it can read =query(page1!A:C, “SELECT A,C,B”, 1) in that example the columns would be ordered in a different order than A B C. I actually explain this in my import range video.
@@whyworksmarternotharder Thank you! So what I am trying to do is a little different. I have section one set up for teachers to select their name. Then from there it takes them to the section that has their students names. From there they will out the form with some info on their kiddos (one submission per student)...however working with 10 different teachers is creating many sections and the columns going too far across to really be able to compare the data. I need to be able to move Teacher B responses so they appear below teacher A responses....then I can look at my students all at once and organize the data. Does that make sense?
@@florenciagirman2357 I understand what you are saying. I still think you could do it. For instance let’s say teacher A is in column B. if the kids info for teacher A is always in column C then you could write the formula as =query(‘Response!’A:Z, “SELECT A,B,C”, 1). Now let’s say teacher B is in column D and the kids info for Teacher B is in column E. You just need the change the formula ever so slightly. =query(‘Responses!A:Z’, “SELECT A,D,E”,1).
@@florenciagirman2357 the other option might be to change the layout of your actual survey. Instead of adding a different section to your survey, you add a choice selector question with each teacher as one of the answers. Then all your teachers would be in one column in the spreadsheet. And then all the answers to the rest of the questions would be in one column as well. Then you just set up a filter.
@@whyworksmarternotharder ok I think what you said above would make sense, the student datat is about 4 questions would that still work? If I do your last suggestion would the teachers need to decipher through all the students or is there a way (w out sections) that they would see just their own? THANK YOU for your guidance. I’m techie but not this much :)
All I want to do. is use a drop down list and produce a report on TV series that I own and update when I have watched them last. Why is my objective so heard?
Came here from the budget spreadsheet video, it has changed my life. Query is an amazing function, thank you for explaining it, especially the miscellaneous syntax like ”select”. Expanding my spreadsheet vocabulary every day!
Good deal. The import range gets even cooler! Glad you are learning!
Also came here from the budget video. Haven't watched this video yet. . . . I'm intrigued by the sneak peak in the other video.
I really dont know why this guy does not have like a million subs already....
That drives me crazy in the forms how new responses don't keep formatting or formulas. I learned the feature you showed a while back, but knowing that is how you suggest to do it too, gave me confidence knowing I was doing it the same way, so thank you. Learned a few more tips in this video too.
Thanks for sharing!
Clear, smart and easy delivering way.. Genius 👍
Great video. Thanks! Loved that last trick to customize the Query function for specific entries.
Thank you!
So helpful! Thank you
I am studying for my Google educator 2, so your clips come in handy, thanks.
That’s awesome! Anything specific you would like to know more about? Maybe I could include it in some of my next videos?
@@whyworksmarternotharder Well not at the moment but I just might get back to you on that, thanks!
Good video. FYI, the formals weren't replaced or delete, a new blank row was added when the form is submitted.
I wish all working professionals see this on their recommendations.
This is just so good and timely.
We started using Chromebooks because we’re working remotely and I know nothing about google sheet. Can we add new responses and pivot on a separate file rather than add new sheets so noone messes up those two sheets?
I noticed that the google sheet is so laggy and it affects all of us working remotely because we share the same files.
Any tips how to fix it? 3 months of data and the file is lagging.
hi!
Could you tell me why query can not show the cell contain both text and number . Its can show one of them only?
would you please help me with this case ( imagine that I have two group of courses A and B and each one of them have 12 class per months , 3 class per week
what is the formula that i need for example to choses ( course A ) will desplay ( saturday , Monday ,Wednesday) and if chose ( course B ) will desplay (Sunday ,Tuesday ,Thursday )
great videos, music break too load!. Thanks for your work.
Can one use a cell reference for the filter as in the formula below?
=query('Form responses 4'!A1:K, "select * where (J= 'A2')", 1)
Yes
it would be great if you could show us to make a form that dumps into an expense tracker spreadsheet, and then how to make a budget dashboard from that. I've ALMOST got it, but where my expenses were negative, now my budget dashboard charts are upside down (-1,000 to zero). I'm thinking in my form I may make 3 number questions, expense, income, and total... or I'm really not sure how to make these graphs work off of my data yet. I will paste a link to the video I started with. My issue is that I want it to also track the balances in my accounts because it doesn't make sense to do all of this work and still have to keep a checkbook somewhere else. I hope that makes sense!
ruclips.net/video/yd-YMYdj6EI/видео.html
Thanks for the info. Just subscribed. Question - what if I want the query to filter out two columns instead of one? Example, what if I wanted only a particular shirt size AND the shirt color to be filtered in the query? How would that formula look (if possible)?
I for the delay in response. It would look exactly like you just wrote it with all cap letters AND
@@whyworksmarternotharder YES! It worked. Thanks!!!
This is really helpful thank you. I am a teacher collecting data on students. Is there a way to write the Query so that the different sections realign on the new sheet? I can explain further if you have a chance. THANK YOU!
Yes there is. You can align columns in any order. For instance it can read =query(page1!A:C, “SELECT A,C,B”, 1) in that example the columns would be ordered in a different order than A B C. I actually explain this in my import range video.
@@whyworksmarternotharder Thank you! So what I am trying to do is a little different. I have section one set up for teachers to select their name. Then from there it takes them to the section that has their students names. From there they will out the form with some info on their kiddos (one submission per student)...however working with 10 different teachers is creating many sections and the columns going too far across to really be able to compare the data. I need to be able to move Teacher B responses so they appear below teacher A responses....then I can look at my students all at once and organize the data. Does that make sense?
@@florenciagirman2357 I understand what you are saying. I still think you could do it. For instance let’s say teacher A is in column B. if the kids info for teacher A is always in column C then you could write the formula as =query(‘Response!’A:Z, “SELECT A,B,C”, 1). Now let’s say teacher B is in column D and the kids info for Teacher B is in column E. You just need the change the formula ever so slightly. =query(‘Responses!A:Z’, “SELECT A,D,E”,1).
@@florenciagirman2357 the other option might be to change the layout of your actual survey. Instead of adding a different section to your survey, you add a choice selector question with each teacher as one of the answers. Then all your teachers would be in one column in the spreadsheet. And then all the answers to the rest of the questions would be in one column as well. Then you just set up a filter.
@@whyworksmarternotharder ok I think what you said above would make sense, the student datat is about 4 questions would that still work?
If I do your last suggestion would the teachers need to decipher through all the students or is there a way (w out sections) that they would see just their own? THANK YOU for your guidance. I’m techie but not this much :)
All I want to do. is use a drop down list and produce a report on TV series that I own and update when I have watched them last. Why is my objective so heard?