- 13
- 44 259
The First Pancake
Canada
เข้าร่วมเมื่อ 10 ต.ค. 2021
Google Suite tutorials to take your projects to the next level.
Our channel is called "The First Pancake" because anything worth doing well is worth doing badly first. That first pancake is always a bit wonky, but the next few are awesome. It's the same with learning new tech: things may not go so well the first time you try a Google Sheets formula, but with practice, you can produce amazing results!
Our channel is called "The First Pancake" because anything worth doing well is worth doing badly first. That first pancake is always a bit wonky, but the next few are awesome. It's the same with learning new tech: things may not go so well the first time you try a Google Sheets formula, but with practice, you can produce amazing results!
Google Sheets: Conditional Multi Drop Down Lists
Today we are creating a multiple drop down list where the values in the second drop down are dependent on the value selected in the first dropdown.
Have you ever been building a spreadsheet and filling out data with a few related sets and you wished there was an easier way? You're in luck -- there is! I was inspired by two other TH-camrs:
@LeilaGharani in this video: th-cam.com/video/7mo4COng7Sg/w-d-xo.html
@KevinStratvert in this video: th-cam.com/video/vh2jdOQ5VLs/w-d-xo.html
I saw what they did and wanted to take it up another notch.
00:00 - intro
00:39 - formulas in this video
00:57 - XLOOKUP
01:31 - SORT
01:58 - ARRAYFORMULA
02:24 - TRANSPOSE
02:54 - let's build!
07:29 - the extra extra
15:46 - outro
---
All music is from Epidemic Sound
Have you ever been building a spreadsheet and filling out data with a few related sets and you wished there was an easier way? You're in luck -- there is! I was inspired by two other TH-camrs:
@LeilaGharani in this video: th-cam.com/video/7mo4COng7Sg/w-d-xo.html
@KevinStratvert in this video: th-cam.com/video/vh2jdOQ5VLs/w-d-xo.html
I saw what they did and wanted to take it up another notch.
00:00 - intro
00:39 - formulas in this video
00:57 - XLOOKUP
01:31 - SORT
01:58 - ARRAYFORMULA
02:24 - TRANSPOSE
02:54 - let's build!
07:29 - the extra extra
15:46 - outro
---
All music is from Epidemic Sound
มุมมอง: 17 921
วีดีโอ
Google Sheets: Use Index and Match Together for Advanced Lookups
มุมมอง 197ปีที่แล้ว
A few weeks ago we did a video on VLOOKUP. Now we are taking it to a new level with INDEX and MATCH. 🔥 While they may look like they do the same, INDEX and MATCH together brings a whole new dimension to your lookups allowing you to return entire rows or columns, or to return values to the left of the search key. This is a game changer when building a great spreadsheet. Learn how to combine form...
Google Sheets: how to use COUNT, COUNTA, and COUNTIF
มุมมอง 2.3Kปีที่แล้ว
In this tutorial, we'll explore three powerful formulas in Google Sheets that can help you work with data more efficiently: COUNT, COUNTA, and COUNTIF. With these formulas, you can quickly count the number of cells in a range that contain numeric data, count the number of non-empty cells in a range, or count the number of cells that meet specific criteria. Whether you're analyzing survey result...
Google Sheets: VLOOKUP tutorial
มุมมอง 87ปีที่แล้ว
VLOOKUP is a staple in Google Sheets and is used time and time again. In this video, I take you step-by-step through the VLOOKUP function. After a short description of what the function does and how to use it, we see real-world examples of how it can be used. All music is from Epidemic Sound
How to Add Colour to Drop-Down List Chips in Google Sheets
มุมมอง 1Kปีที่แล้ว
Set the colours of your status drop-down lists so you can easily track where you are at with each company. Learn Google Sheets while you find your dream job. In this video, we cover: 00:00 - intro - some suggestions for your to-do list 01:09 - how to change your drop-down list options' chip colours 02:43 - some job interview tips Keep your chin up. We believe in you. 🔗 Get a copy of the finishe...
How to Format Currency and Sum a Range of Cells in Google Sheets
มุมมอง 10Kปีที่แล้ว
Format currency and figure out how much you should be asking for when you land that dream job. Learn Google Sheets while you search for your dream job. In this video, we cover: 00:00 - intro - things to consider regarding your expected salary range 00:41 - review of how to add data validation rule for dates 01:03 - how to format currency 01:30 - use SUM to calculate the total of a range of cell...
How to Calculate the Months or Years Between Two Dates in Google Sheets
มุมมอง 2.8Kปีที่แล้ว
Continue building your job search tracker and learn how to calculate the length of time between two dates. Keep learning Google Sheets while you search for your dream job. In this video, we cover: 00:00 - intro to the job durations tab 00:43 - how to format a date 01:48 - how to calculate the number of months between two dates using DATEDIF 02:23 - how to calculate the number of years between t...
Drop Down Lists, Format Dates, and Calculate Days Between Dates in Google Sheets
มุมมอง 2.3Kปีที่แล้ว
Continue building your Job Search Tracker. Create your own workflows with status drop-downs, date formats, and colour coding. Learn Google Sheets while you search for your dream job. In this video, we cover: 00:00 - intro 01:09 - how to create a drop-down list using named ranges 03:18 - date validation and formatting 05:17 - how to calculate the last contact date Time time, we calculate the las...
Build a Job Search Tracker using Google Sheets - Intro
มุมมอง 7Kปีที่แล้ว
Layoffs have been rampant. We've been there and it is terrible. If you are in search of a job, this Job Search Tracker will keep you organized so you don't lose your head in the process. You can learn Google Sheets while you get ready for the interviews. Keep your chin up! 🔗 Get a copy of the finished template: templates.thefirstpancake.ca/l/job-search-tracker
Google Sheets: The Magic Dropdown Tutorial
มุมมอง 707ปีที่แล้ว
Don't you hate when you are using a dropdown to rank options and you put this same option twice? You end up going back to redo all of it. With this Magic Dropdown tutorial that will never happen again. Find out how to remove options from the list after you have used them. Make your life and your rankings easier, more efficient, and impress your boss! We cover FILTER, NOT, and COUNTIF in this tu...
Getting Started With Google Sheets: Data Filtering and Slicers
มุมมอง 76ปีที่แล้ว
We dive into Google Sheets data filters and data slicers in this helpful tutorial. You will get a handle on the basics of how to filter and slice your data for better visibility and organization. We cover the dos and don'ts for each filter type to avoid overwriting when collaborating on a spreadsheet.
Get Started With Google Sheets: Essential Tutorial
มุมมอง 157ปีที่แล้ว
Learn the basics of Google Sheets with this helpful tutorial! Get up to speed on creating spreadsheets, entering data, formatting cells, and more. Credits: Fake Data browser extension: fakedata.pro/ Battleship boardgame image credit: www.huffpost.com/entry/board-games-best-worst_n_5596251
Welcome to The First Pancake
มุมมอง 167ปีที่แล้ว
Master Google Suite of apps and get super powers in your career. Always remember - Anything worth doing well, is worth doing badly first.
That is great! Thanks a lot! I tried this option using ArrayFormula, but did not work...
Thankyou for the help! Appreciate how conscise and helpful it was.
Could you share the file you are working on in the video? It's much easier to follow while clicking yourself :)
Excellent and useful information
Amazing! Great video, it works with +1000 lines!! Thank you!
Is there any way when youre done to sort the sheet now by, for example, name of your friend? I tried doing that in mine and it messed up all my data validation categories. Any advice?
Having the same problem
If you figure this out, can you please let me know? I'm trying to troubleshoot
Question, this worked great until I wanted to sort my main sheet in order from A->Z. It messes everything up - why is that?
Santwant singh
start of the video doenst show how the DD date gets transferred to the To-DO page
yeah i’m also having trouble with this lol
thanks, this helped
Glad to hear it. Let us know if there is anything else you get stuck with and we can make a video about it.
Thank you so much! Your tutorial is the easiest and the most straightforward!
Glad it helped! Let us know if you have any other ideas that we can turn into videos
FINALLY! Thank you for showing how to do this!
Happy to help! Let us know if you have any other ideas you want to see turned into videos.
@@_TheFirstPancake_ Well since you mention it, here's another :) I have a table of 7 columns, each with a named heading. I need a function that will match the text in an adjacent cell against the entire 7-column range, then display the appropriate heading under which that text is found. For context, I'm a pastor logging my sermons, and one column lists the book of the Bible for that sermon. I want the cell to the right to automatically list what genre of writing that book corresponds to, and on another sheet I already have all 66 books sorted into 7 genres. Does that make sense? I'm pretty new at this, so I don't even know what to search for, except it has got to be some variation of an IF function. Thanks in advance!
I love that you made this video. Thank you! I'm struggling with being able to see things well.
Glad it was helpful! Good luck with your job search.
Thank you very much friend, I tested it on a spreadsheet with more than 150 thousand lines and it is working very well!
My goodness, that’s a heck of a spreadsheet. Good for you. What other problems are you looking to solve?
EXACTLY what I needed. THANK YOU!!!
Thank for dropping a comment. Glad we could help. Let us know what other content you need and we can see what we can do.
Can u make vedio for Google Calendar and Google sheet reminder
Happy to give it a try. What are you wanting it to cover?
For the last 1 week I was desperately searching for Multi Dependent Dropdown list function. I got the clarity to a great extent thru this video. My situation: I have a list of 36 provinces of India & Each provinces have 5-35 districts. Now, I am preparing a list of 2600 Trained Technicians (nationwide), Where I want their Districts drop downs should be based on “provinces” selection. This feature is important as this (Master Data) sheet will be accessed by 25 Individual engineers spread throughout the nation and they will take care of listing their Area technicians details. Your idea of keeping a duplicate sheet (hidden) of the main sheet can solve my problem for now I guess. But, I would request, if their could any simpler solution for this topic (like in excel) Tons of Thanx!
We are so happy to hear that this helped you. I'm working on finding a simpler solution. Someone in one thread suggested something involving tables and named ranges. We are trying to figure out the best way to do this. So glad that this worked for what you need right now. please let us know if there is anything else you are stuck with and maybe we can create a video around it.
@@_TheFirstPancake_ Thank you team & I will actively wait for you to come up with some easy alternative of the present demonstrated workaround in G-Sheet. Meanwhile, a support required! do you have any solution for "Multi select" Data validation list. The end result should be comma separated items in the single cell. I understand this feature is available in Excel. But, if you have something similar for G-Sheet, it will be so helpful!
@CprtFM Thank you for your patience. 2 questions. 1) Could you clarify the above. I'm not sure I understand what you mean regarding a data validation list. I can certainly take a bunch of data and add it to a single cell, comma separated but I'm not clear on how this will validate data. 2) Could you confirm that this list if a fairly comprehensive list of provinces and districts in India? vikaspedia.in/education/current-affairs/states-and-districts-of-india
There must be less data repitive method to creating these dependent drop down list. Correct me if I'm wrong but with Excel we can just use =INDIRECT with the dependent cell referenced usually piggy backing off of Named Ranges titled the same as the referenced cell. I feel like Google has made a fairly simple function overly complicated especially for the average user. Personally, I'm really concerned about using this repetitive method and how it will affect speed and functionality with larger files. I wonder if there is an alternative for Sheets similar to INDIRECT as I described for Excel?
Hi Nicolas, I absolutely agree that this could be heavy for the spreadsheet. I've never seen this method using INDIRECT before. Sheets does have INDIRECT. Do you have an example of what this could look like? Let me play with it and see if I can do what you are referring to. ;)
My question is, how do I get something from the drop down list to populate on another sheet? For example I have a drop down list with "Absent, complete, testing". I want "Absent" to generate on another sheet so i can keep track of who was absent.
Hi Estefany, Thanks for reaching out. If you have them in one big sheet, you can move them to another tab using and =FILTER to get just that group, or you can use =SORT if you want to group them. If you need them in completely different spreadsheet files, then you would have to combine =FILTER and =IMPORTRANGE. If you are just looking for totals, you could also use a pivot table to get the overall number of times. I hope this helps.
love it thanks you so much !!
You are very welcome. Please let us know if there is anything else you would like to learn. We love getting content ideas from our community.
This is indeed ONE of the ways to do the multi row dependent drop down list. This alternative is actually 👍👍👍 if we're just seeing from the 'result'. But if we are building a sheet, with the dependent drop down list is just ONE of the features, it's kinda ineffective since the data repeated over and over again -> increasing the memory -> slower sheet Cheers to another pov of spreadsheet formula tricks 🙌
Thanks for the feedback. And absolutely it could bog down the memory here. I'd love to hear any ways that wouldn't bog down the memory. I would secretly love if Google would add the ability to determine the drop down values from a formula right in the data validation. It would make it oh so much easier. In theory it might be possible through an appScript or something like that... but thats something for another video in the future. If there is any topics you would love to see us cover, we'd love to know about it.
Pretty complicated but really well explained
Thanks for the feedback. If there are any topics you would like use to cover, please let us know.
This is a great video
Thanks for the feedback. If there is anything you are looking to do with Google Sheets that we can help with, we would love to hear how we can help.
Glad you enjoy it. We’d love it if you shared with your friends as we grow.
This is a great blend of knowledge and entertainment. Keep it up! I love this content! 😀
Thanks for the feedback. We plan to keep making more. Let us know if you have requests.