My knowledge of Google Scripting and Sheets grew exponentially thanks to this channel. Great job!
@colemanwalker1133 жыл бұрын
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
@karinrosvall-clausen7502 Жыл бұрын
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.
@MichaelSchneider-ec2nd Жыл бұрын
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. 😀
@vandy851010 күн бұрын
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
@pedropellegrini4 жыл бұрын
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?
@pedrohenriquefontesleitao97502 жыл бұрын
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?
@pepemiko3 жыл бұрын
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.
@chonlavitchz-71284 жыл бұрын
OH wonderful . how to write formula in the head "TOTAL"
@cabinetix3 жыл бұрын
Great video, thank you so much! Will this work for a sumif with a dynamic Sum Range? My sumif problem was solved by the formula Alan shared in this video kzbin.info/www/bejne/rp-Th4athpWJpsU, wondering how I can make it work with google forms like you have taught...
@muhammadarsyad21534 жыл бұрын
You are awesome ! n... u are the best teacher 😉
@sharitambasco97804 жыл бұрын
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.
@wfgkc22703 жыл бұрын
Is there any way to get that those calculations to display in the form? something like 'your order will but $4'
@Beautymovesindia Жыл бұрын
you should try =query(Main!A:I).... in a1 in new sheet. this is easy . (in case i have rename my response sheet name into "Main"
@matteovelicogna79483 жыл бұрын
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
@upaliwijeratne8696 Жыл бұрын
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
@gonefishing20063 жыл бұрын
Nice job. I'm just getting started with Google Sheets. Long time Excel user but self taught.
@carolbaskas69297 ай бұрын
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?
@suhritbiswas94442 жыл бұрын
Can you please help me with this for the same situation ? =(((BM2*4)+(BN2*3)+(BO2*2)+(BP2*1))*10)/((SUM(BM2:BP2)*4))
@wandadevine20513 жыл бұрын
Great video! Didn't really need this info, but couldn't stop watching!
@akivachakansky52273 жыл бұрын
Trying to work with Google sheets I have products from different companies with their codes I want to look up in product code For duplicate if so I wanted to go to coast and calculate which one is cheapest by how much then give me how much and what company or give me if possible the cost on each of the companies for example company a cost $.20 more than Company B and Company c coast $.30 less than Company b or something like that
@freepeople182 жыл бұрын
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
@Rajashekar362 жыл бұрын
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
@premierwater54743 жыл бұрын
very good explanation ... keep up the good work man !!!
@surajjadhav55262 жыл бұрын
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. 🙏🏼
@yansen53122 жыл бұрын
array formula didn't work with OR functions. How to solve this problem ?
@markgarrett29713 жыл бұрын
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!
@dewanimasi68003 жыл бұрын
How to submit user data to spreadsheet when user click next on google form multi section🙏#googleform
@MuhammadShoaib-jk3vt Жыл бұрын
Thanks Great video i just to know how to use concatenate and =googletrasnlate function in it please help me in this regard.
@rjowen2 жыл бұрын
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.
@ExcelGoogleSheets2 жыл бұрын
:)
@tarundeepgrover7705 Жыл бұрын
Customers should know the prices and the total before they buy. Can you show the total in the form also ?
@NeelNarayan3 жыл бұрын
Amazing job man! Glad i found you or rather thanks to YT algo for suggesting your channel. Cheers :)
@ExcelGoogleSheets3 жыл бұрын
Awesome, thank you!
@MeenuMathew3 жыл бұрын
But I got same result for all columns.My equation is =ARRAYFORMULA(IFS(ROW(A:A)=1,"Overall Score",A:A="","",TRUE,5*SUM(F:F,H:H,J:J,L:L,P:P,R:R,T:T,V:V,X:X)/50))
@ahtapot19232 жыл бұрын
Very very helpful ! Thank you very much !
@ThomasvandenBosch3 жыл бұрын
Hello, I encounter one problem in my Array as it does not wants to change row in the calculation: =ARRAYFORMULA(IFS(ROW(B:B)=1,"Week", A:A="", "", TRUE, IFERROR(INDEX(Dates!$A$2:A,MATCH(A2,Dates!$B$2:B),1),""))) It keeps on calculate A2 for each row but I wants it to count for each row as A2, A3, A4,... Is there a solution?
@genaminin4 жыл бұрын
super. this is exactly what I was missing !!!
@KingTirto8 ай бұрын
Great tutorial and thank you for sharing!
@CprtFM3 жыл бұрын
Thank u so much for this lesson...!!
@xitcode3 жыл бұрын
Thank you. Learned something new.
@knazarbe3 жыл бұрын
Is it possible to show sum of price directly in google form during filling process?
@josemarcelalopez52703 жыл бұрын
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.
@Improve_3 жыл бұрын
I have question if i make frmula in excel sheet how can i merge it withe Google doc
@yaoguo38964 жыл бұрын
Great video!!! thank you so much!!!
@imr4nkhan Жыл бұрын
🔥🔥🔥 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 !
@peppeqSK3 жыл бұрын
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
@shayvaughn11603 жыл бұрын
You're a genius. Thank you!
@ABC-ow5zs3 жыл бұрын
Thank you so much sir!! You make my life easier, i really appreciated it 😊
@r.saneie807611 ай бұрын
Thank you so much for organized teaching method
@RMenon9684 жыл бұрын
Brilliant! Thank for teaching the world. 🙏
@nickohdub2 жыл бұрын
Amazing! Master of your skill.. it's nice to see multiple options for completing the same task and which methods you prefer over others.
@daklaker22873 жыл бұрын
How can i press f4 on mac keyboard sir? i press cmd T but my safari return to the favourite bar.
@ExcelGoogleSheets3 жыл бұрын
fn+F4
@jessicamora76652 жыл бұрын
Thanks for this video! Is there another tutorial which Google Forms shows the price of the product you selected previously?
@ExcelGoogleSheets2 жыл бұрын
I'm not sure what you mean.
@jacobomondi49622 жыл бұрын
query function works perfectly fine without combining so many confusing formulas
@ExcelGoogleSheets2 жыл бұрын
I'm not sure what one has to do with the other, but so long as it works for you keep using it.
@SpaceiumGames2 жыл бұрын
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.
@ExcelGoogleSheets2 жыл бұрын
I have clock-in, clock-out video on the channel that does this.
@pradeepparichha81113 жыл бұрын
Good evening sir 🙏 Thanks for the video. Please add a video how to make the name of the particular respondent appear automatically in confirmation message that we receive after submitting the Google forms. Thanks. Pradeep Parichha
@debjyotikanjilal26102 жыл бұрын
Content was very good however font size is too small
@wangming7583 жыл бұрын
Unfortunately, I am still searching how to do a self made order form with google form which has calculation function.
@ExcelGoogleSheets3 жыл бұрын
You'll have to make a Web App kzbin.info/aero/PLv9Pf9aNgemt82hBENyneRyHnD-zORB3l , google Forms doesn't support this at the moment.
@marksawesomeadventures2 жыл бұрын
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!
@FranciBahamult3 жыл бұрын
I need to change the title on the second line what change do I have to make in ROW(A:A)=1?
@tanveerhussain38814 жыл бұрын
I love all video
@Vin-nq4fz2 жыл бұрын
This video resolved my problem. I subscribed, liked and would like to say thank you!
@ExcelGoogleSheets2 жыл бұрын
:)
@amaarafarms68813 жыл бұрын
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.
@Monnan_3 жыл бұрын
Thank you so much for making the video, I wanted to follow your instructions but it didn't work. I could have learned it if you had cooperated with me.If you give me a mail I would add you so you could see the matter.
@hassanaitabbou24783 жыл бұрын
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
@bocobox4 жыл бұрын
Can U think of Google sheet formula combination something similar to what if analysis function in Excel? Please suggest me. Thanks.
@jesscarmona34834 жыл бұрын
Great Video.... but how to handle a Purchase Order with a list of Item?? In this example you only select one product at the time. I would love to see a solution for this.
@aNDy-qh1em3 жыл бұрын
Thanks sir! That is just superb technique! The main issue i have with Google Sheets is to have connected to corporate mysql database.
@miltoszoumekas75673 жыл бұрын
That was brilliant, thanks for the information covered in this video!
@silvestrecamposano6317 Жыл бұрын
Thank you for the technique!....
@pawansharma-wo3xh11 ай бұрын
Thank you so much. great help
@zr2ee1 Жыл бұрын
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
@RaichoNikolov2 жыл бұрын
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?
@SonjoeJoseph4 жыл бұрын
docs.google.com/spreadsheets/d/1TUcsv_fs5DDFZ7gotY2WdM1IcoO3gR8G8wXedYVLffk/edit#gid=0 Dear Sir, Please go to the above link and see the file. I need to create a dynamic equity curve line chart using the drop down selection list which is given as "Start Date" and "End Date". My idea is as and when i enter new data the line graph should get updated . Also when I change the "Start date" and "End date" the chart should give the result according to the selection. Please do help me out. Thanks and regards, Sonjoe
@ExcelGoogleSheets4 жыл бұрын
I think I have something like that in the last part of this video kzbin.info/www/bejne/aqbKY32ojrhop9k
@SonjoeJoseph4 жыл бұрын
I have seen that video but didn't get a solution to my ans. I need a chart so that if i change the Start Date and End Date its gives me the chart accordingly to the date specified data. Can u kindly work on my google sheets file which is shared with u.
@BrianTiessen Жыл бұрын
Well done! Thank you very much!
@suhritbiswas94442 жыл бұрын
How to use Count if function in the same situation ? Currently, the formula is =(COUNTIF(I2:BH2,"*Critical*"))
@@ExcelGoogleSheets Thanks a lot for instant reply, not expected this quick. Fixed a huge problem with the help of your video. Subscribed...
@ChimCu29123 жыл бұрын
Is it possible to show the calculation for the user after submitting form ?
@ExcelGoogleSheets3 жыл бұрын
Not with Google Forms, possible if you use a Web App.
@mateenchoudhary96404 жыл бұрын
Hello Sir, can you make one more one google sheet. (Send Bulk whatsapp msg from google sheet)
@paolobena2 жыл бұрын
Thank you so much, have been sutck on this problem for almost an hour!!
@paxandjoe-buildingandbreak35303 жыл бұрын
EXCELLENT VIDEO!!! The Vlookup info is exactly what i needed, not a lot of solid info on this topic on the web
@homejonny93264 жыл бұрын
Thanks for the video. I wonder why Google is so dedicated to office suite apps, but do not care about an integrated relational DBMS like Microsoft Access or OpenOffice Base...
@AtiqZabinski3 жыл бұрын
9:35 OK, but this IFS formula is unnecessarily complicated. Why create a condition just to test for the row being the first row when you can put multiple formulas inside curly braces? Just do D1 ={"Price" ; IFERROR(VLOOKUP($B2:$B, Prices!$A:$B,2,0)) } This will put "Price:" in D1 and perform the VLOOKUPS in D2:D. The IFERROR will take care of the N/As where Columns A-C are blank. Since that leaves no more conditions to test, there is no need for clunky old IFS and its requisite TRUE condition.
@AtiqZabinski3 жыл бұрын
{ "Header name" ; ARRAYFORMULA for row values } is how I typically write formulas like this, when you have a single ARRAYFORMULA producing all the values under the Header Row. It keeps the formula out of harm's way, so you can safely sort your results or whatever without fear of throwing them off.
@ExcelGoogleSheets3 жыл бұрын
Sure, you can do that. I've demonstrated your technique in certain videos as well.
@ExcelGoogleSheets3 жыл бұрын
I think people miss the fact that I create videos for educational purpose, it's not meant to be the only or the best way to do something.
@AtiqZabinski3 жыл бұрын
@@ExcelGoogleSheets There is almost always more than one way to solve a geek problem, and often there is no obvious best way, just trade-offs of one advantage versus another.
@kipaskdk3 жыл бұрын
Good one. Can i use name range instead of Prices!$A;$B? tks in advance
@enelis44923 жыл бұрын
Hi, I tried this technique on my spreadsheet and it didn't work and I'm assuming it has to do with the differences in formula. My goal is to separate each item sold, if you will, into different columns on a different sheet, and say who is buying said item.. and every time I try to check for text or use ISBLANK in my if arguments, along with the ROW=1 thing, it comes up with pretty much the same issue as I was having where the google form adding a row messes up the formulas, with the following error: "IFS has mismatched range sizes. Expected row count: 1000. column count: 1. Actual row count: 1001, column count: 1." If there's any suggestion you have for me to get around it I would very much appreciate it. When I do =ARRAYFORMULA(IF(ISBLANK(Form!M:M),"--",Form!B:B)) it works just fine, but as soon as I make it an IFS statement with the ROW thing it gives the error.. Is there any different way to hide the formula with a title? Or is there a different way to check for blanks other than ISBLANK, or to only show the buyer of said thing when it contains text? I tried M:M="" but that didn't work either.
Thank you for taking the time to properly show each step! The breakdown of eliminating the errors was really helpful!
@ksecurity734 жыл бұрын
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 ?
@chrisjohnstone79083 жыл бұрын
Great! subscribed!
@thiagocalbo7073 жыл бұрын
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.
@ExcelGoogleSheets3 жыл бұрын
VLOOKUP approximate match
@douglarson23222 жыл бұрын
Thanks. This video just changed my life.
@thinkpositive70453 жыл бұрын
Sir, can we use that price and total column as data for pdf and email it like autocrat?
@davybigfly2 жыл бұрын
Thank you it worked and I learned some good stuff here
@ExcelGoogleSheets2 жыл бұрын
Great to hear!
@Sophoan13 жыл бұрын
thanks so much
@AmjadJavedLashari6 ай бұрын
You make my days save.. Thanks
@lafamillecarrington2 жыл бұрын
Another great video. Can you reorder the entries so that the new orders are at the top of the spreadsheet?
@sajidshahzad79952 жыл бұрын
very helpfully. Thank you
@DiniShanti2 жыл бұрын
Wow thank you...
@danielwritzlzinidzini73424 жыл бұрын
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!
@ExcelGoogleSheets4 жыл бұрын
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.
@ChrisTyler-cv9gy7 ай бұрын
Just what I needed at minute 11:51
@CindyFrancisco-j3f Жыл бұрын
This just changed my life ! Thank you !
@cheewurz4 жыл бұрын
Another Great Lesson!!!
@josephkieft71812 жыл бұрын
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!!