i guess Im randomly asking but does someone know of a trick to log back into an Instagram account? I somehow forgot my login password. I love any assistance you can offer me
Dude, you blew my mind!!!! Curly brackets and Arrayformulas with Ifs and vlookups - Shut the Front Door!!! .... pow!!!!! Dude I've been using excel as a "power user" for 20+ years... I'm back to a beginner after this! I bow to your greatness!
Great tutorial! I especially appreciated the teaching structure of starting with simple examples and building up to more complex and efficient ways to implement the autofill functionality.
Thank you so much for this tutorial. I appreciate how you don't just show a technique, but how to apply it in a real-world situation. And eerily enough, I was just about to do a search this week on how to put an array formula in the header row.
This - is a perfect tutorial. (1) it was exactly what I wanted. (2) You built it from scratch. (3) You showed the process of starting simple and adding complexity/simplicity where needed. Here, have a like and a subscribe from me.
This Video is one of the most USEFUL Videos I have seen you do. What I like most about your Videos is in each Video you are throwing in a lot of Tips and Tricks that I don't see in other Videos. Thanks!
I have seen lots of help videos on Google Sheets. You are the best at explaining Sheets!!!! You are very easy to follow along with and explain things very well. Thank you very much and keep it up!!
Great video, exactly covering the problem I'm looking to solve. And the first tutor that does not use copy&paste when reusing 90% to 100% of his already written formulas at least 3 times. 😀
Thank you so much for this video! I spent hours looking for this answer. Truly appreciated. Thanks for taking it step by step and showing us common mistakes.
This tutorial is extremely helpful. I liked the way you built it up from its simplest form into something more efficient. I'm stuck on a project I'm doing where I'm trying to collect whether or not a student ate a school breakfast or lunch and how many they ate per month. Each student fills out the form during their 6th period. The tally has to be student specific taken in a specific teacher’s class. The form has a drop down for each student’s name, teacher’s name, date, and number of school breakfasts/lunches eaten. Watching your video, I now know how to use an array formula to collect and calculate data in real-time. I'm struggling with this…i need to be able to quickly glance at the spreadsheet by 6th period teacher and be able to tell how many breakfast/lunches each specific student ate in a any given month. Can you help?
When putting a header name on an arrayformula, what i usually do is this: =arrayformula({"header name";iferror(formula i want)}). The "header name" becomes a header and the formula is going to be applied to the cells below.
Always learning great new things from you, keep up the good work. Trying to find a video on tabulating checkbox data from a form. For some reason up until now I never had to do it or realize how difficult it is
This was the exact solution for my problem, thank you! I'm automating personalized certificates that require google form submissions that include a count so I can apply a level based on the count (bronze/silver/gold, etc.) This formula applies the correct level after a submission comes in and I use Autocrat to kick out the personalized certificate via email. Thanks for saving me hours of volunteer time!
Thank you so much, I looked for this function for few weeks and I finally found at here! Thank you so much, and I hope you can keep make more useful videos!
When you create My Calculations sheet, are you then able to sort it manyally using filter? Or the data is in the exact order as they are entered in the form?
Thanks for the great video! I have only one question. What if i need to say to Google Sheets that a cell has to be equal to another cell on a different spreadsheet and has to change everytime i add another row in that column. So for example: "C2" in the sheet "Calculation" has to be equal to "A2" in the sheet "Responses" but when i add a new response, that would be in "A3" (responses sheet); the value in "C2" (calculation sheet) has to change with it. So "C2" in "Calculation" sheet changes when i add a new row in the column "A" of "Responses" sheet. Is that possible? Thank you
Very nice video! Congratulations! I'm trying to generate a report after the form submission and I'm getting the calculated columns as "blanks". For example, I have a Form with 2 questions, Client and Price. var timestamp = e.values[0]; var client = e.values[1]; var price = e.values[2]; So, I've created a new calculated column (with array formula) which is Price*(any number). When I try to collect this new value in the variable "var total = e.values[3];", I get a blank value... Do you know why? Is there a delay?
Great Tutorial. Got me so much farther than others. Greatly appreciate it. only thing is that whenever i submit an form it actually "pushes" the formula cell down and doesnt use it (cell stay blank). I should mention that I am using this form to populate the excel sheet but then creates a document from the cell fields. I can share a screenshot and also have the script code if that helps. Cheers
Thanks for a great video. 1. Why do you prefer the second method? What is the benefit of separating the 2 TABS? 2. Did you say at the beginning of the video that there are several methods instead of VLOOKUP, what other formulas can you do?
Great video, thank you. If I use the columns from 'Calculations' tab in a google app scrip, what would their value be? The first-page first column is (0) but I don't know what the second tab columns should be referenced as? Hope that makes sense!!!
I have an issue on very extent spreadsheet: the array gets really heavy on cumulative array calculations! Is there a way to use script to do cumulative calculations just on form submit, than pass the calculated value instead of formula? Thanks! I've learned a lot on this channel!
A Brilliant tutorial. I learned a lot from it. I need to know how can I change the formula when the preces changes every month. Eg: January price is 100 and February Price is 125. So I want to multifil the quantities bought in January with the 100 and February quantities with price 125 . Kindly educate me how to do it. Thanks
Great video. But much simpler, add this in the header row (though I'd use a named range): ={{"Price"};{iferror(arrayformula(vlookup(B2:B,NamedLookupRange,2,0)),"")}} You can use this method to put several column headers and different data sets for each (literals shown below), in a single cell in a header: ={{"species","Color","Weight"};{"fish";"dog"},{"orange";"brown"},{"5 gms";"50 lbs"}} ={{"species","Color","Weight"};{"fish","orange","5 gms"};{"dog","brown","50 lbs"}} (the key thing is where you use commas versus semicolons. The former delimits columns, the latter delimits rows) You can also return discontiguous columns from a range this way, e.g. columns 3 and 5 in a named lookup range: ={{"Col3","Col5"};{iferror(arrayformula(vlookup(A2:A,NamedRange2,{3,5},false)),"")}}
I don't like using IFERROR, because I like to know the difference between errors and blanks. Also, you don't need those extra brackets =arrayformula({"Price";iferror(vlookup(B2:B,NamedLookupRange,2,0),"")})
@@ExcelGoogleSheets Correct. I'm habituated to using 'em largely because in a formula that both creates multiple columns and aggregates multiple row sources, they help demarcate. I'll also split a formula into multiple lines to make it more readable. Some of these things can get pretty unwieldy. BTW, we're recommending your channel to all our teachers/clinicians/everyone (we're a special ed non-profit). We IT types are often face-palming what we see in the field. We believe your truly excellent instructional videos will help -- and you've shown us some good tricks too. Thanks for being ahead of the pack. :-)
It is very helpful to me. In google form, question to upload file attachment need to google account login. Is there any way to upload file attachment by google apps script without google account logining ?
🔥🔥🔥 What a cool video and easy tips. Thanks for your efforts they are most appreciated. SUBSCRIBED! .... do you have a way where different departments update the same google form but relative to their section. For example the service reception logs in customer data and job number and the technicians log their respective job start and end times to that same job number? .... or would it be having separate forms to one Google sheets file and then creating the "My Calculations" Tab to arrange all data? Thanks !
Dear Sir, I want to create a pdf and semd email after submitting google form with the addtional data. I followed your all videos and i want to add addtional column in pdf doc and send email. It is can possible can i connect another formulas column in that procesa? Please help. 🙏🏼
I use this a lot. It's very useful! The only problem is when we use lots of array formulas cause it gets slow. It would be nice to use apps script to define a formula in the first/second row and have it copied and the value inserted automatically on form submit.
This is an amazing video. Thank you. Is it possible to display a message or receipt after submission, I mean displaying all the data you submitted along with Item, Quantity, Price and Total. I would be glad if that is possible.
In my case I've build a personality test with 60 questions and answers goes from. A. Xxxxxxxxx to Dxxxxxxxx full sentences as options. Each letter represents a number and is also divided by sections so we measure 6 different characteristics in each section so I have no idea if is possible to create a formula to calculate the totals of A and B ... D. Any help would be appreciated
Thank your sir for this good video I hope if you can help me to insert 2 combolist in my google forms in a way that the second one would be linked to the choice made in the previous one. So elements of the second combo depent of what i chose in the first Thank you sir
Great help, but I am thinking, how to put all this into an ready Invoice, where: 1) I have 3 items that I sell, 2) each item (service) is ordered only once (Qty 1) but it can be ordered e.g. all three in a single form , 3) I need to have each ordered item then on separate row in the invoice with price next to it (maybe template based on excel), 4) price summary under and finally 5) all this export into PDF
Is there a method to calculate these respective values and then, show it as a summary after submit in the google form, like showing total amount of the bill at the very end of the form or in the forms' confirmation message.
@@ExcelGoogleSheets Please share the link using web app for the above query ??? also Im unable to add multiple products with qty from single user using as it vlookup func just matches value so in total col. its shows ERROR :/
I have to pull data from branch and day wise and it's cumulative in other column...say in A column sales per day and in other column job cards per day I have to pull these data and show it day wise and its cumulative in other column...any solution will save our time greatly appreciable
Hey Great video! I am trying to get the googlesheet to automatically populate the "month" column by extracting it from the time stamp using the TEXT function. Unfortunately arrayfunction is not automatically updating the rows until I drag down the formula. I request you to please please tell me a way I can find a solution to this. I would be extremely grateful.
I'm new at spreadsheeting and I've been struggling to do something that is supposedly simple: There is a rating scale consisting of 14 items (going from 0 to 4), I want to do something like (if result ≤ 17, then "Average", if result is in between 17-21, "Moderate"). How should I approach it? I keep messing it up and in my head it is something simple.
How do I do this every other line, so I use the google forms as a clock in and out system, and I want to automatically calculate the difference in in time between the first timestamp and the second timestamp.
My knowledge of Google Scripting and Sheets grew exponentially thanks to this channel. Great job!
i guess Im randomly asking but does someone know of a trick to log back into an Instagram account?
I somehow forgot my login password. I love any assistance you can offer me
Dude, you blew my mind!!!! Curly brackets and Arrayformulas with Ifs and vlookups - Shut the Front Door!!! .... pow!!!!! Dude I've been using excel as a "power user" for 20+ years... I'm back to a beginner after this! I bow to your greatness!
Thank you for taking the time to properly show each step! The breakdown of eliminating the errors was really helpful!
Great tutorial! I especially appreciated the teaching structure of starting with simple examples and building up to more complex and efficient ways to implement the autofill functionality.
Thank you so much for this tutorial. I appreciate how you don't just show a technique, but how to apply it in a real-world situation. And eerily enough, I was just about to do a search this week on how to put an array formula in the header row.
This - is a perfect tutorial. (1) it was exactly what I wanted. (2) You built it from scratch. (3) You showed the process of starting simple and adding complexity/simplicity where needed.
Here, have a like and a subscribe from me.
:)
This Video is one of the most USEFUL Videos I have seen you do. What I like most about your Videos is in each Video you are throwing in a lot of Tips and Tricks that I don't see in other Videos. Thanks!
I have seen lots of help videos on Google Sheets. You are the best at explaining Sheets!!!! You are very easy to follow along with and explain things very well. Thank you very much and keep it up!!
Thanks!
This is by far the best tutorial I've seen on this
Great video, exactly covering the problem I'm looking to solve. And the first tutor that does not use copy&paste when reusing 90% to 100% of his already written formulas at least 3 times. 😀
Thank you so much for this video! I spent hours looking for this answer. Truly appreciated. Thanks for taking it step by step and showing us common mistakes.
Nice job. I'm just getting started with Google Sheets. Long time Excel user but self taught.
Amazing! Master of your skill.. it's nice to see multiple options for completing the same task and which methods you prefer over others.
This tutorial is extremely helpful. I liked the way you built it up from its simplest form into something more efficient. I'm stuck on a project I'm doing where I'm trying to collect whether or not a student ate a school breakfast or lunch and how many they ate per month. Each student fills out the form during their 6th period. The tally has to be student specific taken in a specific teacher’s class. The form has a drop down for each student’s name, teacher’s name, date, and number of school breakfasts/lunches eaten. Watching your video, I now know how to use an array formula to collect and calculate data in real-time. I'm struggling with this…i need to be able to quickly glance at the spreadsheet by 6th period teacher and be able to tell how many breakfast/lunches each specific student ate in a any given month. Can you help?
Great. But and if we need to return this values to the GoogleForm? Is it possible in real time while the user is using the form?
I have not seen any Goole forms tutorial this deep ...awesome!!
👍
Thankyou, I was searching this since long time, now I got it. You are making life easier
This just changed my life ! Thank you !
I can't like this video enough. Thank you so much for doing it
:)
Thanks. This video just changed my life.
Thank you! Your videos provide excellent information for beginners like myself to begin using sheets more effectively.
This video resolved my problem. I subscribed, liked and would like to say thank you!
:)
When putting a header name on an arrayformula, what i usually do is this:
=arrayformula({"header name";iferror(formula i want)}). The "header name" becomes a header and the formula is going to be applied to the cells below.
Thank you so much, have been sutck on this problem for almost an hour!!
You make my days save.. Thanks
You are awesome ! n... u are the best teacher 😉
Always learning great new things from you, keep up the good work. Trying to find a video on tabulating checkbox data from a form. For some reason up until now I never had to do it or realize how difficult it is
Amazing job man! Glad i found you or rather thanks to YT algo for suggesting your channel. Cheers :)
Awesome, thank you!
This was the exact solution for my problem, thank you! I'm automating personalized certificates that require google form submissions that include a count so I can apply a level based on the count (bronze/silver/gold, etc.)
This formula applies the correct level after a submission comes in and I use Autocrat to kick out the personalized certificate via email. Thanks for saving me hours of volunteer time!
Thank you so much for organized teaching method
Thank you so much, I looked for this function for few weeks and I finally found at here! Thank you so much, and I hope you can keep make more useful videos!
Great!
This is what i was looking from last month to put name with formula.
Thank you so much.
👍
When you create My Calculations sheet, are you then able to sort it manyally using filter? Or the data is in the exact order as they are entered in the form?
Thank you so much. I needed this array function to complete my sheet!
Wonderful!
Great tutorial and thank you for sharing!
very good explanation ... keep up the good work man !!!
Brilliant! Thank for teaching the world. 🙏
Another great video. Can you reorder the entries so that the new orders are at the top of the spreadsheet?
Thanks for the great video! I have only one question.
What if i need to say to Google Sheets that a cell has to be equal to another cell on a different spreadsheet and has to change everytime i add another row in that column.
So for example: "C2" in the sheet "Calculation" has to be equal to "A2" in the sheet "Responses" but when i add a new response, that would be in "A3" (responses sheet); the value in "C2" (calculation sheet) has to change with it.
So "C2" in "Calculation" sheet changes when i add a new row in the column "A" of "Responses" sheet.
Is that possible?
Thank you
Very useful tutorial. Thanks a lot!
:)
Very nice video! Congratulations!
I'm trying to generate a report after the form submission and I'm getting the calculated columns as "blanks".
For example, I have a Form with 2 questions, Client and Price.
var timestamp = e.values[0];
var client = e.values[1];
var price = e.values[2];
So, I've created a new calculated column (with array formula) which is Price*(any number).
When I try to collect this new value in the variable "var total = e.values[3];", I get a blank value...
Do you know why? Is there a delay?
Great Tutorial. Got me so much farther than others. Greatly appreciate it.
only thing is that whenever i submit an form it actually "pushes" the formula cell down and doesnt use it (cell stay blank). I should mention that I am using this form to populate the excel sheet but then creates a document from the cell fields. I can share a screenshot and also have the script code if that helps.
Cheers
Thanks for this video! Is there another tutorial which Google Forms shows the price of the product you selected previously?
I'm not sure what you mean.
Very very helpful ! Thank you very much !
Thanks for a great video.
1. Why do you prefer the second method? What is the benefit of separating the 2 TABS?
2. Did you say at the beginning of the video that there are several methods instead of VLOOKUP, what other formulas can you do?
If you decide to modify the form it's less likely to have a disaster and easier to fix things if you keep it on a different tab.
You can also use non-array formulas this way.
Hello, can I hire you to help me build my sheet?
You're a genius. Thank you!
Thank u so much for this lesson...!!
Thanks Great video i just to know how to use concatenate and =googletrasnlate function in it please help me in this regard.
That was brilliant, thanks for the information covered in this video!
Thanks for all of your videos, when I first started I couldn't even do simple conditional formatting
Thank U.. Now I see myself thinking how to apply ur skill to my workplace.
Great video, thank you. If I use the columns from 'Calculations' tab in a google app scrip, what would their value be? The first-page first column is (0) but I don't know what the second tab columns should be referenced as? Hope that makes sense!!!
I have an issue on very extent spreadsheet: the array gets really heavy on cumulative array calculations! Is there a way to use script to do cumulative calculations just on form submit, than pass the calculated value instead of formula? Thanks! I've learned a lot on this channel!
If you have bunch of blank rows below your data, remove them. That should make it faster.
You can of course do it with a script.
super. this is exactly what I was missing !!!
Thanks sir! That is just superb technique! The main issue i have with Google Sheets is to have connected to corporate mysql database.
Thank you. Learned something new.
Thank you so much sir!! You make my life easier, i really appreciated it 😊
Thank you it worked and I learned some good stuff here
Great to hear!
Is there any way to get that those calculations to display in the form? something like 'your order will but $4'
A Brilliant tutorial. I learned a lot from it. I need to know how can I change the formula when the preces changes every month. Eg: January price is 100 and February Price is 125. So I want to multifil the quantities bought in January with the 100 and February quantities with price 125 . Kindly educate me how to do it. Thanks
Can U think of Google sheet formula combination something similar to what if analysis function in Excel? Please suggest me. Thanks.
You really helped me fella
Well done! Thank you very much!
Great video.
But much simpler, add this in the header row (though I'd use a named range):
={{"Price"};{iferror(arrayformula(vlookup(B2:B,NamedLookupRange,2,0)),"")}}
You can use this method to put several column headers and different data sets for each (literals shown below), in a single cell in a header:
={{"species","Color","Weight"};{"fish";"dog"},{"orange";"brown"},{"5 gms";"50 lbs"}}
={{"species","Color","Weight"};{"fish","orange","5 gms"};{"dog","brown","50 lbs"}}
(the key thing is where you use commas versus semicolons. The former delimits columns, the latter delimits rows)
You can also return discontiguous columns from a range this way, e.g. columns 3 and 5 in a named lookup range:
={{"Col3","Col5"};{iferror(arrayformula(vlookup(A2:A,NamedRange2,{3,5},false)),"")}}
I don't like using IFERROR, because I like to know the difference between errors and blanks.
Also, you don't need those extra brackets
=arrayformula({"Price";iferror(vlookup(B2:B,NamedLookupRange,2,0),"")})
Another thing to keep in mind is that your formula will do some weird stuff if there are no submissions in the form spreadsheet yet.
@@ExcelGoogleSheets
Correct. I'm habituated to using 'em largely because in a formula that both creates multiple columns and aggregates multiple row sources, they help demarcate. I'll also split a formula into multiple lines to make it more readable. Some of these things can get pretty unwieldy.
BTW, we're recommending your channel to all our teachers/clinicians/everyone (we're a special ed non-profit). We IT types are often face-palming what we see in the field. We believe your truly excellent instructional videos will help -- and you've shown us some good tricks too. Thanks for being ahead of the pack. :-)
Thank you! Was hung up on this the other day
Dude, you rock! Awesome video series, MANY THANKS.
Thanks, this is really helpful!!
😀
It is very helpful to me. In google form, question to upload file attachment need to google account login.
Is there any way to upload file attachment by google apps script without google account logining ?
🔥🔥🔥 What a cool video and easy tips. Thanks for your efforts they are most appreciated. SUBSCRIBED! .... do you have a way where different departments update the same google form but relative to their section. For example the service reception logs in customer data and job number and the technicians log their respective job start and end times to that same job number? .... or would it be having separate forms to one Google sheets file and then creating the "My Calculations" Tab to arrange all data? Thanks !
Another Great Lesson!!!
Dear Sir, I want to create a pdf and semd email after submitting google form with the addtional data. I followed your all videos and i want to add addtional column in pdf doc and send email.
It is can possible can i connect another formulas column in that procesa?
Please help. 🙏🏼
Is it possible to show sum of price directly in google form during filling process?
query function works perfectly fine without combining so many confusing formulas
I'm not sure what one has to do with the other, but so long as it works for you keep using it.
Great video!!! thank you so much!!!
i for the life of me cant find a video thatll show it automatically add the submissions of the rows and give a total like a live leaderboard
Thank you so much. great help
Just what I needed at minute 11:51
I use this a lot. It's very useful! The only problem is when we use lots of array formulas cause it gets slow. It would be nice to use apps script to define a formula in the first/second row and have it copied and the value inserted automatically on form submit.
You could do that or you can delete blank rows in the bottom of your spreadsheets.
Génial !! Formidable !!! MERCI BEAUCOUP :-)
This is an amazing video. Thank you. Is it possible to display a message or receipt after submission, I mean displaying all the data you submitted along with Item, Quantity, Price and Total. I would be glad if that is possible.
Thank you for the technique!....
Sir, can we use that price and total column as data for pdf and email it like autocrat?
very helpfully. Thank you
Just what I needed thanks!!!
In my case I've build a personality test with 60 questions and answers goes from. A. Xxxxxxxxx to Dxxxxxxxx full sentences as options.
Each letter represents a number and is also divided by sections so we measure 6 different characteristics in each section so I have no idea if is possible to create a formula to calculate the totals of A and B ... D.
Any help would be appreciated
Thank your sir for this good video
I hope if you can help me to insert 2 combolist in my google forms in a way that the second one would be linked to the choice made in the previous one.
So elements of the second combo depent of what i chose in the first
Thank you sir
Absolutely brilliant. Thank you so much!
Great help, but I am thinking, how to put all this into an ready Invoice, where: 1) I have 3 items that I sell, 2) each item (service) is ordered only once (Qty 1) but it can be ordered e.g. all three in a single form , 3) I need to have each ordered item then on separate row in the invoice with price next to it (maybe template based on excel), 4) price summary under and finally 5) all this export into PDF
Good one. Can i use name range instead of Prices!$A;$B? tks in advance
Thank you so much for this!
Glad it was helpful!
Is there a method to calculate these respective values and then, show it as a summary after submit in the google form, like showing total amount of the bill at the very end of the form or in the forms' confirmation message.
No. You'll have to use a Web App.
@@ExcelGoogleSheets Please share the link using web app for the above query ??? also Im unable to add multiple products with qty from single user using as it vlookup func just matches value so in total col. its shows ERROR :/
This is great, thankyou.
Great to hear!
Nice vide but it was very easy!! I appreciate your work
Is it possible to show the calculation for the user after submitting form ?
Not with Google Forms, possible if you use a Web App.
*Thank you* . Just thank you.
I have to pull data from branch and day wise and it's cumulative in other column...say in A column sales per day and in other column job cards per day I have to pull these data and show it day wise and its cumulative in other column...any solution will save our time greatly appreciable
Hey Great video!
I am trying to get the googlesheet to automatically populate the "month" column by extracting it from the time stamp using the TEXT function. Unfortunately arrayfunction is not automatically updating the rows until I drag down the formula. I request you to please please tell me a way I can find a solution to this. I would be extremely grateful.
I'm new at spreadsheeting and I've been struggling to do something that is supposedly simple:
There is a rating scale consisting of 14 items (going from 0 to 4), I want to do something like (if result ≤ 17, then "Average", if result is in between 17-21, "Moderate"). How should I approach it? I keep messing it up and in my head it is something simple.
VLOOKUP approximate match
Excellent video, thank you.
Thanks!
How do I do this every other line, so I use the google forms as a clock in and out system, and I want to automatically calculate the difference in in time between the first timestamp and the second timestamp.
I have clock-in, clock-out video on the channel that does this.