![TopTech KIT (Tech simplified)](/img/default-banner.jpg)
- Видео 232
- Просмотров 93 008
TopTech KIT (Tech simplified)
Добавлен 30 авг 2021
TopTech KIT Ideas is a channel dedicated to providing free learning to anyone interested in using technology to enhance productivity. Topics range from office applications like Excel, Word, Power Point. Google Sheets, Google Docs and other applications we use in real life.
Results are strongly linked to productivity.
Results are strongly linked to productivity.
Mastering Tables in Google Sheets: Using Table Reference in Dropdowns & Formulas.
Hello, Google Sheets enthusiasts! In this tutorial, we'll walk you through the essential steps to reference columns in a table, create dropdown lists using the referenced columns, and utilize these references in various Google Sheets formulas. Follow these simple steps to become a Google Sheets pro!
📊 Table of Contents:
Introduction
Using Table references to extract the entire table with headers and without header
Using Table references to extract columns from a table
Using Table references t to a column to create a Dropdown List
Using Table Column References in Google Sheets Formulas
Recap
Don't forget to like, share, and subscribe to our channel for more Google Sheets tips, tricks, and tutoria...
📊 Table of Contents:
Introduction
Using Table references to extract the entire table with headers and without header
Using Table references to extract columns from a table
Using Table references t to a column to create a Dropdown List
Using Table Column References in Google Sheets Formulas
Recap
Don't forget to like, share, and subscribe to our channel for more Google Sheets tips, tricks, and tutoria...
Просмотров: 187
Видео
Unlocking the Power of Tables in Google Sheets: New Feature Tutorial
Просмотров 2352 месяца назад
🚀 Introducing the New Table Feature in Google Sheets! 📝 The latest addition in Google Sheets - the all-new Table Feature ! This game-changing tool is designed to make your data management more intuitive and efficient than ever before. Whether you're organizing a small dataset or managing extensive information, this feature simplifies your workflow with advanced functionalities and a user-friend...
Mastering the UNIQUE Function in Google Sheets: A Comprehensive Tutorial
Просмотров 452 месяца назад
Hello, Sheets enthusiasts and data connoisseurs! In this enlightening tutorial, we're going to dive into the world of Google Sheets and explore one of its most powerful and versatile functions - the UNIQUE function. We'll cover what it is, why you should use it, when to employ it, and of course, how to use it like a pro! 📊 What is the UNIQUE Function? The UNIQUE function in Google Sheets is a b...
Revolutionize Your Finances: Master AI-Powered Reconciliation with CoPilot for Finance
Просмотров 434 месяца назад
Are you tired of spending countless hours manually reconciling payments and invoices? Say goodbye to tedious tasks and hello to efficiency with CoPilot for Finance, an innovative Excel add-on powered by artificial intelligence (AI). In this tutorial, we'll show you how to leverage AI to effortlessly identify matched and unmatched transactions, streamlining your financial processes like never be...
Mastering Google Sheets: Extracting Specific Days of the Month with Advanced Functions
Просмотров 584 месяца назад
Unlock the full potential of Google Sheets with advanced data extraction techniques in this comprehensive tutorial! We'll dive deep into using functions like EOMONTH, DAY, FILTER, and SEQUENCE to efficiently extract specific days of the month, such as Tuesdays in April 2024. Discover how to leverage the EOMONTH function to dynamically determine the last day of each month, making your formulas a...
Unlock the POWER of MAP in Google Sheets: 4 Amazing Transformations!
Просмотров 1976 месяцев назад
Unlock the POWER of MAP in Google Sheets: 4 Amazing Transformations! Tired of repetitive formulas? Buckle up, spreadsheet warriors! We're diving into the MAP function, your new secret weapon for automating tasks and simplifying data manipulation in Google Sheets. In this video, we'll conquer 4 practical examples that showcase MAP's versatility: Example 1: Miles to Kilometers in a Flash! Convert...
Google Sheets Mastery: Effortlessly Merge Data with 3 Techniques, Tackle Empty Rows like a Pro!
Просмотров 1356 месяцев назад
In this tutorial, we're diving deep into the world of Google Sheets to show you how to seamlessly combine data from different sheets using three powerful approaches: enclosing data in {}, using vstack, and leveraging the query function. Plus, we'll discuss the preferred approach, especially when dealing with data that includes empty rows. Introduction Approach 1: Using VSTACK Approach 2: Enclos...
Build a Dynamic Loan Amortization Schedule in Google Sheets! (It Grows & Shrinks!)
Просмотров 1207 месяцев назад
Say goodbye to static spreadsheets and hello to loan mastery! This video unlocks the secrets of building a dynamic loan amortization schedule in Google Sheets. Witness the magic as your schedule automatically changes based on your loan, interest rate, or even the loan period itself! Learn how to: Craft a flexible schedule: Watch your table expand or shrink as you adjust the loan term, making it...
Google Sheets Security Nightmare? Fix It in Minutes with This Secret Script!
Просмотров 1367 месяцев назад
Fret no more! This comprehensive tutorial will equip you with the power to password-protect your Google Sheets using Google Apps Script, the ultimate key to spreadsheet security. ️ In this tutorial, learn step-by-step how to add an extra layer of security to your valuable data in Google Sheets by protecting it with a password using Google Apps Script! Overview: Do you want to restrict access to...
Unlocking Data Magic-Google Sheets: Advanced Manipulation with WrapRows, WrapCols, and More!
Просмотров 1437 месяцев назад
Uncover the power of data manipulation in Google Sheets with this comprehensive tutorial on WrapRows, WrapCols, Index, and VStack functions! 🚀 In this video, we'll break down the basics of WrapRows and WrapCols, essential functions for reshaping and restructuring data in Python. Learn how to use these functions to enhance your data manipulation skills and take your programming to the next level...
Level Up Your Spreadsheets: Interactive Formatting with Checkboxes in Google Sheets. Switch on/off
Просмотров 1418 месяцев назад
Welcome to another exciting Google Sheets tutorial! In this video, we'll guide you through the process of creating a dynamic checkbox that toggles conditional formatting in cell E3. Specifically, we'll demonstrate how to apply formatting based on values greater than and less than 50 using a custom formula. What You'll Learn: ✔️ Create a Checkbox in Cell E3 ✔️ Set Up Conditional Formatting for V...
Revolutionize Your Email Game: Google Sheets + Apps Script Automation Masterclass
Просмотров 718 месяцев назад
Learn how to supercharge your email workflow with Google Sheets and Apps Script! In this tutorial, we'll guide you through the process of sending personalized emails to your customers directly from your Google Sheet. Whether you're managing a mailing list or sending invoices, this automation will save you time and effort. ⭐️ Key Steps Covered: Set up your Google Sheet: Create a Google Sheet wit...
Unlocking Efficiency: Generate Dynamic QR Codes in Google Sheets Without Add-ons | Chart API
Просмотров 7978 месяцев назад
Welcome to our tutorial on creating QR codes in Google Sheets without the need for any add-ons! In this video, we'll walk you through a simple yet effective method using the Google Chart API. Whether you're managing inventory, creating event badges, or enhancing your data visualization, this tutorial is for you! Covered Introduction Setting Up Your Google Sheet Understanding the Google Chart AP...
Excel Mastery: Transform Your Data with Dynamic Color Charts for Goal Success!
Просмотров 1028 месяцев назад
In this visually captivating Excel tutorial, we unveil a powerful technique to enhance your data visualization experience! 🌈 Watch as we demonstrate how to create an interactive chart that dynamically changes colors based on achieving your set target. 🚀 Say goodbye to static charts and hello to a new era of dynamic data representation! Learn how to set a target in your Excel sheet and witness t...
Mastering Data Dynamism: Unleash the Power of Google Sheets for Dynamic Top Value Selection
Просмотров 1209 месяцев назад
Welcome to another exciting Google Sheets tutorial! 🚀 In this video, we'll explore a powerful technique for dynamically finding the top values in an array using Google Sheets' functions. If you've ever worked with datasets containing two columns, such as 'Name' and 'Amount,' and wanted to easily select and display the top values, this tutorial is for you. In this tutorial, we'll guide you throu...
Mastering Project Management: Build an Efficient Project Tracker in Google Sheets
Просмотров 3899 месяцев назад
Mastering Project Management: Build an Efficient Project Tracker in Google Sheets
Effortless Holiday Tracking in Google Sheets | Conditional formatting and Custom formula
Просмотров 5399 месяцев назад
Effortless Holiday Tracking in Google Sheets | Conditional formatting and Custom formula
Mastering Data Transformation: Combine 4 Query Functions |Advanced Query Functions
Просмотров 1899 месяцев назад
Mastering Data Transformation: Combine 4 Query Functions |Advanced Query Functions
Mastering Data Merge/join tables in Google Sheets: VLOOKUP and ARRAYFORMULA Tutorial"
Просмотров 27310 месяцев назад
Mastering Data Merge/join tables in Google Sheets: VLOOKUP and ARRAYFORMULA Tutorial"
Mastering Data Analysis: Filter Pivot Tables with Year-Based Dropdowns in Google Sheets
Просмотров 41110 месяцев назад
Mastering Data Analysis: Filter Pivot Tables with Year-Based Dropdowns in Google Sheets
How to Master SUMPRODUCT in Google Sheets: Advanced Tips for Excel-Like Functionality
Просмотров 30910 месяцев назад
How to Master SUMPRODUCT in Google Sheets: Advanced Tips for Excel-Like Functionality
Google Sheets Data Filtering Made Simple: Dual Drop-Down Lists Tutorial
Просмотров 1 тыс.10 месяцев назад
Google Sheets Data Filtering Made Simple: Dual Drop-Down Lists Tutorial
Automate Google Sheets with a Data Entry Form with Google Apps Script
Просмотров 9 тыс.11 месяцев назад
Automate Google Sheets with a Data Entry Form with Google Apps Script
Mastering Data Transformation |Pivot and Unpivot | FLATTEN, SPLIT, & QUERY Functions in GoogleSheets
Просмотров 34611 месяцев назад
Mastering Data Transformation |Pivot and Unpivot | FLATTEN, SPLIT, & QUERY Functions in GoogleSheets
Mastering Data Consolidation: Merge & Sync Multiple Sheet Tabs in Google Sheets with Ease!
Просмотров 23511 месяцев назад
Mastering Data Consolidation: Merge & Sync Multiple Sheet Tabs in Google Sheets with Ease!
How to Filter Data with Partial Name Matching using FILTER , SEARCH , ISNUMBER, BYROW Functions
Просмотров 37211 месяцев назад
How to Filter Data with Partial Name Matching using FILTER , SEARCH , ISNUMBER, BYROW Functions
Mastering Data Filtering in Google Sheets using Checkboxes and FILTER, TRANSPOSE, UNIQUE & more.
Просмотров 2,9 тыс.11 месяцев назад
Mastering Data Filtering in Google Sheets using Checkboxes and FILTER, TRANSPOSE, UNIQUE & more.
Google Sheets import charts and Tables from Google Sheets to Google Docs. | No code
Просмотров 43911 месяцев назад
Google Sheets import charts and Tables from Google Sheets to Google Docs. | No code
Effortless Google Sheets to Excel Sync: Mastering Data Transfer
Просмотров 3,3 тыс.Год назад
Effortless Google Sheets to Excel Sync: Mastering Data Transfer
Combining and Splitting Data in Google Sheets | Ultimate Data Management Tutorial
Просмотров 3,9 тыс.Год назад
Combining and Splitting Data in Google Sheets | Ultimate Data Management Tutorial
Big thanks to you. It was simple and easy to understand. Subscribed
Great. Glad that you found it simple and easy to understand. Thanks for subscribing.
Thank you!
WELCOME. THANKS FOR WATCHING
Very cool! In real life situation, I would just do a Pivot table. But this is a good for educaiton.
Thanks for the comment. Another way of looking at it . It is good to have alternatives,
Spent an hour finding out how to do this. Thank you!
Glad I could help!
Please help me. When I try to run the editrecord function, it says Error Exception: Range not found. What should I do to dataSheet.getRange("A"+parseInt(newVals[])).setvalue(newVals[]); Sir ?
wow we were so confused for 30 mins but your video changed our lives!
Glad that this helped
Thank you for the simplified guide.
well received
I like your video
thanks
I got the following error when using this formula :-( =SPARKLINE(countif(C4:F4,true),{"chartype","bar";"max",5;"color1",G6}) Sparkline requires more data points. Expected: 2, Found: 1.
Dime esta API está obsoleta, como se puede actualizar?
Thank you for your tutorial, this is so thorough and easy to follow. I hope you're having a great week!
You are so welcome!
can you write down the code please?
I have a table that uses and array formula and a VLOOKUP to calculate and post scores in a golf tournament. I use an IMPORT HTML to obtain the field of entries and their respective scores. The first day it works perfectly. The second day the (HTML) site inserts in a row about 2/3 down “the projected cut line”. This blows up my form (#N/A results) and VLOOKUP Table. I can’t edit the HTML table to ignore the inserted row or can I? Or can I modify the range to ignore the inserted row? If so, how? Looking for any suggestions.
is it an empty row that was added?
So helpful- thank you so much!
Am glad your found this helpful
it's not dynamic, static.
PLEASE HELP EXPLAIN WHAT YOU MEAN WHY THIS?
@@toptechkit dynamic qr codes are getting provided by servers, their qr codes are actually code of a memory in their servers and they are able to change that memory cell when ever you want. but static qr codes are just a translation of alphabet to numbers.
Seriously the best Sheets training I have come across. Thank you.
Thanks for this comment. I appreciate it
this works in most cells but the QR does not show up in some cells even though the data is the same. Ideas?
please recheck to ensure that the formula is giving the correct cell reference.
Hello Sir
Hello
Out of all the tutorials I watched, you were the only person to actually explain how checkboxes work! Thank you
Great to hear. Thanks for watching
Thank you so much 🙏
you are welcome
Thank you! Very informative and easy to follow :)
Glad that you found it helpful
Good teachers are those who explain difficult tasks to their students in an easy manner, and you are a good teacher. Thank you Sir 🙏🙏🙏
Thanks.
Great video. Super clear instructions. Thank you!
I want the ID to function with a format like: 23456934-K and 23345123-9 any thoughts?
Thank you, this video helped me very much! I'm new to the sheets, so I'm not as good. I didn't know how to use COUNTIF. Thank you again and take care :)
Provide code
Very very useful in understanding query context in Google Sheets
Sir, please help me. When I try to run function searchrecord, it say TypeError: Cannot read properties of undefined (reading '0'). What should I do? I follow all your code but get that error while you are not. Please help me
when you enter the ID to search move the cursor from that cell and then click search. it should work
Exactly what I was looking for "Google sheets payslip automation no add ons" "Google sheets payslip using functions" had to go through a lot before I came here ty.
Glad that you found this helpful.
Thank you this is super helpful. Is there any way to see the total for each category as well? TIA
Hello, thank you so much for the amazing content. I tried to do it as it was explained, however, it did not pull the results as expected. Are there any errors in this formula? =QUERY(DATABASE_CALLS!$A$2:$J,"SELECT * WHERE A='"&B1&"' "&IF(B2="","","AND B='"&B2&"' ")&IF(B3="","","AND D='"&B3&"' ")&IF(D1="","","AND I='"&D1&"' ")&IF(D2="","","AND H='"&D2&"' ")&IF(D3="","","AND G='"&D3&"' ")&IF(F1="","","AND E='"&F1&"' "),1)
Thank you so much. This is exactly what I was looking to do to create a resource for my students. I've made my filter and it's working beautifully. Making this brought me so much joy. Thank you! You are a great teacher! Thank you for explaining what each new part did. I learned a lot.
Thanks for watching. Grateful that found this helpful
Thanks 🌱🍃
Grateful
Great tutorial
How are you adding Row ID in the Column E? Is this automated? I have tried using =ArrayFormula(IF(A1:A="", "", ArrayFormula(row(A1:A)))) , but this only works if there are no empty rows at the bottom.
Thanks for watching. This is what I use. =query(data!A2:E,"select A order by A desc limit 1")
Hi Sir, it seems you minute 42:39, it seems you have cut some steps before (cell H3). In addtion, how to get Row ID cummulative number ? thanks
I'm preparing a weekly pay slip. Can you help me to keep a running record on save to pdf and other features
Hi Sir, it seems you minute 42:39, it seems you have cut some steps before (cell H3). In addtion, how to get Row ID cummulative number ? thanks
To get the Row ID, I use " =ROW()" as one of the values to be inserted when adding values. This is done in the code.
@@toptechkit Kindly show us how to do that practically
👉🔔
i have problem @ 56;22, . it said that on line 42 has an error caused by cells is not defined...what should i do?? thanks
@toptechkit looks like you are using variable cells in your code but you have not defined it. define using either let cells =["B2", "B3" ] for example or const cells =["B2", "B3" ]. ensure all the cells where you are picking the data from are defined.
I found this to be very clear and extremely helpful! Thanks so much!
Thanks @nadoerr90. Glad that you found it helpful.
very nicely done - congratulations and thank you very much
Thanks for the comment
Great tutorial! Very handy. Is it possible to make formula for filter or slicer to display needed data by checkboxes right in main data set, not creating additional set on right?
i couldnt run the script, like at 24.58......it says "ReferenceError: cells is not defined..addRecord @ Code.gs:15". could you explain?
please carefully check the code on row number 15 to ensure that it has no error.
looks like you are using variable cells in your code but you have not defined it. define using either let cells =["B2", "B3" ] for example or const cells =["B2", "B3" ]
Great video ! Detailed lesson and video. If I ask. Is there way where we can sync the data entry to google documents? It's like automated form. I hope you can answer my question
Thanks for watching. Do you mean a form in Google Documents and then send data to Google Sheets?
@@toptechkit I mean Google sheets to google docs.
@@jbd7207 watch this video and see if it addresses your concern. studio.ruclips.net/user/videots4TecZj0Hk/edit
very nice teaching style - congratulations and thanks very much
Goodmorning Sir, my Name is Joseph Aniebor . I am An Internal Auditor from Abuja Nigeria. i would love to have a conversation with you in respect of Data entry validation a project that i have been on for over a month. I would love a personal conversation. Please how can i reach you Sir?
Hi Joseph, Thanks for reaching out. Please send me details on the email address bob.rwamagigi@gmail.com. Then will see the next steps.
What if i edit that search data & want to save as new data i.e. without changing previous data
yes, you can change the search data, ensure you change the ID such that you don't have two items with the same ID. After amending the search data click on the ADD button and not the EDIT button to save a new entry.
@toptechkit it still doesn't work. It skipped the cell beneath each day, but it also skipped the second week and the third week.