![sheetsbyolan](/img/default-banner.jpg)
- Видео 116
- Просмотров 46 863
sheetsbyolan
США
Добавлен 1 фев 2024
Google Sheets templates, formulas, tips etc.
beacons.ai/sheetsbyolan
beacons.ai/sheetsbyolan
Automated Bill Pay Tracker Time-Based Apps Script Trigger Setup
This is a tutorial to add the time-based apps script trigger for the Automated Bill Pay Tracker Template. Once set up, the automation that copies any values in the "To Add" tab to the new empty row in the "Pending Bills" tab.
Note: If you do not have the tracker, you can find the full tutorial at the end of the video
Note: If you do not have the tracker, you can find the full tutorial at the end of the video
Просмотров: 0
Видео
Automated Team Task Tracker Upgrade from Tutorial
Просмотров 322 часа назад
Having a centralized task tracker that can be used by an entire team simultaneously can be game changing. One thing that sets Google Sheets apart from Excel is its superior collaboration ability so why not make collaborating on tasks the new norm? This video is the customization tutorial to set up this tracker for your specific team. It is based on you building the tracker based on my original ...
Automated Team Task Tracker Tutorial From Team Task Tracker Template
Просмотров 272 часа назад
Having a centralized task tracker that can be used by an entire team simultaneously can be game changing. One thing that sets Google Sheets apart from Excel is its superior collaboration ability so why not make collaborating on tasks the new norm? This video is the customization tutorial to set up this tracker for your specific team. It is based on you purchasing the "Automated Team Task Tracke...
Become a MASTER of IMPORTRANGE including Hacks and Shortcuts!
Просмотров 107День назад
Exclusive to Google Sheets, the IMPORTRANGE formula is like no other. It allows you to use live data from different spreadsheets in the spreadsheet you're working on. Since it's live, when the data updates in the external spreadsheet, the data in your spreadsheet will automatically update as well. Copy and Paste could never. There are endless use cases for this formula. But first, you need to u...
Student Loan Calculator with Daily Interest Full Walk Through Overview
Просмотров 50День назад
Student Loans are predatory. The worst part though is that there is next to no transparency with regards to paying off your student loans. I created this calculator to see how much your loan details will change based on different payment amounts. Don't have extra money now but thing you will in the future? You can plan ahead to see how much interest you save and how much quicker you can pay off...
Upgraded Automatic Task Tracker In Excel with VBA Automation
Просмотров 1,1 тыс.14 дней назад
I use the Google Sheets version of this task tracker and it has made me one of the most organized people in the company. It takes minimal time to upkeep and that amount is saved (plus more) by being more efficient with my tasks. I also never run the risk of of forgetting to do something. This is the full tutorial to build it from scratch. You can also buy a prebuilt template in my shop instead ...
Automatically Sort the Data Combined from Multiple Tabs into a Single List
Просмотров 5521 день назад
In short, the way you sort your data will depend on the formula you used to combine your tabs into a single list. If you use the Query formula, you'll just need to add an ORDER BY clause. If you use the Filter formula, you'll just need to add a Sort formula to it. Hope this helps! 00:00 - Intro 00:18 - Query Formula 00:52 - Filter Formula with Sort
How to combine data from different tabs into a single list in Google Sheets
Просмотров 3521 день назад
If you work with spreadsheets and haven't needed to combine data from different tabs yet, it's coming so you should know how to handle it. Data isn't always as clean as we need it to be so it's important to understand how to manipulate the data into a workable format for our needs. Here are 4 ways to combine data from different tabs. Two ways to combine the data, and two ways to exclude those p...
Job Application Tracker - Free Template - Google Sheets
Просмотров 9521 день назад
Job Application Tracker - Free Template - Google Sheets
Weekly Budget Tracker- Full Walk- Through
Просмотров 6428 дней назад
Weekly Budget Tracker- Full Walk- Through
Non- Automated Task Tracker (Google Sheets)
Просмотров 12828 дней назад
Non- Automated Task Tracker (Google Sheets)
Automatic Task Tracker Walk-Through (Google Sheets)
Просмотров 42028 дней назад
Automatic Task Tracker Walk-Through (Google Sheets)
Import Images Dynamically - Real World Example
Просмотров 63Месяц назад
Import Images Dynamically - Real World Example
Automatic Olympic Medal Counter Full Tutorial
Просмотров 82Месяц назад
Automatic Olympic Medal Counter Full Tutorial
Automated Content Planner Full Tutorial
Просмотров 107Месяц назад
Automated Content Planner Full Tutorial
How to create dependent dropdowns with multiple sublists
Просмотров 159Месяц назад
How to create dependent dropdowns with multiple sublists
Create a clear button on without knowing code
Просмотров 46Месяц назад
Create a clear button on without knowing code
XLookup is really the only lookup formula you need to know
Просмотров 57Месяц назад
XLookup is really the only lookup formula you need to know
Use Dynamic Formulas to Elevate your Spreadsheet Instantly
Просмотров 293Месяц назад
Use Dynamic Formulas to Elevate your Spreadsheet Instantly
Step-by- Step Upgrade your Continuous Flow Task tracker with this step-by-step tutorial
Просмотров 6472 месяца назад
Step-by- Step Upgrade your Continuous Flow Task tracker with this step-by-step tutorial
How I prepare for my performance review!
Просмотров 202 месяца назад
How I prepare for my performance review!
How to add a column to customize your task tracker without breaking the automation
Просмотров 302 месяца назад
How to add a column to customize your task tracker without breaking the automation
Upgraded Continuous Flow Task Tracker with Apps Script (Google Sheets Only)
Просмотров 2,5 тыс.2 месяца назад
Upgraded Continuous Flow Task Tracker with Apps Script (Google Sheets Only)
Here are 3 ways to use XLookup instead
Просмотров 262 месяца назад
Here are 3 ways to use XLookup instead
Impress your boss by creating a search function from multiple tabs using Google sheets
Просмотров 1482 месяца назад
Impress your boss by creating a search function from multiple tabs using Google sheets
3 ways to customize your Google Sheets task tracker without breaking the automation
Просмотров 963 месяца назад
3 ways to customize your Google Sheets task tracker without breaking the automation
Did Google Sheets change something or is there a difference between languages? The first function didn't work for me and I only got parsing errors. After some trial and error I got "=IF(ISBLANK(C3);;MAX($A$2:OFFSET(A3;-1;0))+1)" to work. Edited to add missing $-lock
@@loveshates8049 it’s always been like that. At least to my knowledge. The formatting will depend on the location of the spreadsheet. If you want to follow along in real time exactly as I have it, just change the location of your spreadsheet to the US. Then you’ll be able to use , instead of ;
Does any of this functionality work with Smartsheets? We're onboarding now and I want to be ahead of the curve utilizing a task board like this with reporting features.
This doesn't work. I even tried the exact example you used.
When I am updating something to 'done' under progress/status it is not moving to the done tab. Any thoughts?
Now share the link to the sheet
The time stamps are only working on some lines. Does anyone know why?
Hello, I love this and have it set up but was wondering how would I add more tabs? I tried a couple things but seem to break the sheet.
I have found that parked jobs that change status do not move back into the list.
@@michaelcorbett3926 hey! This version doesn’t have the the instructions for setting up the parked task. The apps script from this video doesn’t have the parked functionality. Are you using this tutorial or the most recent tutorial?
This will work with other types of data as well! Drop some ideas in the comments to help others brainstorm
for the 'extensions' section, i am getting a 404 error when i click extensions-app scripts
Make sure you’re only logged into one Google account when you do that part. Otherwise it can give an error message
for some reason once its parked I can't get it to move back to the task list? either using ready or pending?
Hey! I think there was an error with the code linked for some people. I’ve updated it on my GitHub. If it doesn’t work still, email me and I can send over the template
Hello can it automatically change sheets tabs when it's a new month?
It depends on the naming conventions you use for each tab. Is the tab named after the current month?
What would you like to see next?
@sheetsbyolan I have a sheet that i use to mark if an employee is absent. This sheet has all the months, Aug, Sept, Oct... etc for the tabs. I have it so it automatically changed month when it's a new month. I want to like this absent sheet to time report sheet. So it automatically fills out the time sheet when someone is out. I found a formula for the importrange function to change automatically when it's a new month. Will check if it works when it's September.
For now, it will only highlight the row when someone is out but maybe I can find a way to write absent instead of highlighting the row with conditional format.
this is the formula im using =IMPORTRANGE("link",text(today(),"mmm")&"!D13")
everything works on the base spreadsheet from but the code on Parked doesn't move the item back to the Task List
@@25thedave this tutorial for this version doesn’t have the parked tab. Are you using the code with parked? If so, see the instructions on my most recent tutorial. The instructions will be slightly different
Have you done anything with email automation? for example, if I have a task that is not completed, I am looking to have an email sent to my inbox as a reminder
@@morganjones9040 I know it’s possible but I haven’t played around with it before. Maybe ChatGPT or Gemini will be able to provide the code needed!
There may be really obvious answer to this question, but I'm going to ask it anyway, can you explain what your intention for "PARKED" is/was so I can better understand how to use it? Thank you!
@@BibblyJibbly parked is for the tasks that you can’t work yet due to time constraints or you’re waiting on someone else to complete their part. That way you can keep the task in mind but it’s not cluttering up your active task list
I luckily don’t have student loans but my girlfriend does. This is real slick, I’ll have to use it to show how much money she can save by paying off these loans ASAP. Thank you!
I don’t either luckily but my wife does and this calculator helped me figure out a plan finally. Hope it helps your girlfriend too!
this is exactly what i'm trying to do but still cant get it to work even with using the formula in the description, any ideas? i have something like 10 tabs to filter data from... it seems to pull only from the first tab and once i start to try and do the sexond part of the forumla i get a ref error
@@kellinquinnscheese check out my video on combining ranges I posted recently. It shows a way simpler than vstack that works better. I recommend using the curly brackets with the query formula to remove the blanks since you’re combining so many ranges. I recommend combining all of the ranges into one list before trying to add the search formula to it so you can pinpoint any errors
This thing awesome. I'm excited to start tracking my life. I noticed that when you deleted row 1 from the Parked and Done tab, I kept getting a circular reference error any time a row is moved from Task List to the Done tab. I fixed it just but adding back an empty row to Done and Parked tab. Not a big deal for me.
I cant seem to get the completed number to update when i change/ enter information. When i entered a date in the done sheet it went up but not on the task list sheet
Same here and can’t for the life of me figure out why lol
@@weiredreject if you want to email me a screenshot of your formula I can help troubleshoot
@@Yeahnoyeahuhhu if you want to email me a screenshot of your formula I can help troubleshoot . Sheetsbyolan@gmail.com
Thank you so much for this very detailed tutorial. Quick question, is it possible to create the tracker in a way that the tasks moves into different tabs ( like tasks through phases until done.). Thank you again!
@@vanessaproject5006 check out the upgrades in this video. I think it’s what you’re looking for. The code may be slightly different but the concept is the same ruclips.net/video/ErpvPzOGwxg/видео.htmlsi=Du1Rx9uC0uct2zrX
Thanks for creating this! Super super helpful :) I tried moving all the columns over to the right and for the most part, everything works except for the Parked Tasks back into the Task List. Not sure if this is correct? if (range.getColumn() == 5 && e.value == "Parked") { var recurringValue = sheet.getRange(row, 6).getValue(); if (recurringValue !== "Recurring"){ var targetSheet = e.source.getSheetByName("Parked Tasks"); var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).copyTo(targetRange); sheet.deleteRow(range.getRow()); } } if (range.getColumn() == 5 && e.value == "Ready") { var recurringValue = sheet.getRange(row, 6).getValue(); if (recurringValue !== "Recurring"){ var targetSheet = e.source.getSheetByName("Task List"); var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).copyTo(targetRange); sheet.deleteRow(range.getRow()); }
Hey! I think there was an error with the original code for some people. I updated it on my GitHub. Try it out and if it still isn’t working, email me and I’ll send over the template
Everything about the automation and timestamps works fine but when tasks are moved to the Parked tab, even when the status is changed to Pending, it stays in the Parked tab and does not move back to the Task List. Same issue for the Done tab.
Hey! The trigger for moving a task from the parked tab to the task list is “Ready”. Can you try selecting that to see if the automation works?
@@sheetsbyolan I'm having the same issue even selecting ready it stays on the parked tab
Same here. I chamged list to “ready” like @sheetsbyolan said and still no change.
@@monkeyf1uff757 I see you put "ready" instead of "Ready". Can you confirm the dropdown option is capitalized correctly?
@@smithbryanw1 I see you put "ready" instead of "Ready". Can you confirm the dropdown option is capitalized correctly?
Hi! I’m having a couple issues. 1. The completed counter only updates if the task is recurring. 2. The task isn’t moved into the “Parked” tab 3. When I run the code a tab appears that doesn’t for you.
@@s0urfru1t hey! 1) The completed counter will only reflect done non recurring tasks if they’re on the done tab. The task completed date also needs to be within 7 days of the start date listed on the task list. Is that the case? 2) are the timestamps being added and the tasks moving to the done tab? It could be that the automation isn’t live. 3) What tab appears?
Were you able to get #2 resolved? I can't get any of my tasks to move to the parked or done tabs
Didn’t know excel could do that! Thanks!
It's not Excel, it's Google Sheets
Beautiful!!! Any advice for someone that has zero experience but wants to learn these in excel
@@gd4628 watch videos on social to get introduced to concepts. You don’t know something is possible until you see it for the first time. Then practice by taking online classes. If you have a favorite creator that has a course and want to buy from them that’s great. There are a ton of free courses though and if you have a library card a lot of them allow access to LinkedIn learning for free!
The link is not working :/
@@sslash26 oh no! Which link?
Great video! Is there anyway I can send the recurring tasks to a section instead of another tab?
@@yadanalin it’s easiest to just drag down the row to where you’d like it to be in your list. Is there a specific use case you’d need to send it to a section instead?
Great video and really useful though having a slight issue with the time stamps as when it comes to the tables it just puts it in a date format dd/mm/yyyy and doesnt give me the option for months. Though to be honest its not a big issue
@@Parakka from my understanding it defaults to the location default of the spreadsheet. If you change the location that should fix your issue
=Query function doesn’t show in formula options, 365 office
@@mzubairrafiq8220 Query is exclusive to Google Sheets. I don’t know of an excel alternative
Very demure, very mindful
This is very helpful me to track my task, but I'm stuck in the at Parked when I am changing the task into parked it doesn't move in the Parked Tasks sheet. did i do wrong in the formula?
So glad you're finding it helpful! For the parked tasks, make sure that you don't have an accidental space after the Parked in the dropdown. Also make sure it's capitalized as normal. It has to match the Apps Script code exactly to work. Assuming you upgraded continuous flow task tracker code from my git hub it should work from there. Let me know if you're still having trouble.
@@sheetsbyolan I'm done with the tracker. Thank you🙂
@@MaryJaneIlac Amazing! Let me know how you like it once you get rolling with it
What would you like me to cover next?
@@sheetsbyolan what about the filter formula in excel.
What would you like to see next?
Hi thank you so much for this sheet! this is going to make my life so much easier at work. started with the sheet but after doing my test to see if everything is working as I'm going along, I'm stuck to when a task is at parked, when changing to ready the line doesn't move back to task list. what am i doing wrong?
@@TheSonica91 double check that you don’t have an accidental space after Ready in the dropdown and that it’s Capitalized like normal. Those are the typical culprits. If you’re still having trouble feel free to email me and share your copy so I can help troubleshoot. My email is sheetsbyolan@gmail.com
@@sheetsbyolan problem resolved! Thank you so much!😁😅
What do you want to see next?
Thanks. It would be nice if you could share a sample spreadsheet.
Thanks for the feedback! I’ll try to integrate this going forward. In the meantime, you can use Gemini or ChatGPT to auto generate dummy data for you to practice with!
Fantastic video! Your content is truly engaging and informative; it's clear you put a lot of effort into it. However, I noticed that your thumbnails might not be doing justice to the quality of your videos. I'm a professional thumbnail designer, currently working with established RUclipsrs like Dr. Tanvi Mayur Patel (361k subs), Dr. Sahaj Joshi (215k subs), and Healthy Forever (77k subs). I believe with the right thumbnails, your content could reach even more viewers. I'd love to help you level up your channel’s visual appeal. Looking for your response ☺️
What should I make a video on next?
Lolol love those pseudonyms.
😉
Great Video... It's exactly what I'm trying to do. Pull Data from different Tabs based on inventory location. However, I couldn't get it to work. Any chance you can put the FORMULA in the Description? I believe I copied it out perfectly but it was still generating an error. on my end. Thanks in advance!
Just added the formula. I wasn't able to include a not equals to sign in the description due to youtube character descriptions. Make sure to read the note to know the work around. Let me know if you're still having trouble.
@@sheetsbyolan THAT WORKED LIKE A CHAMP! Only issue I had was the Formula didn't like any extra ROW spacing in the Sheet Tab. I had to remove all the extra rows that doesn't have data in them and it returned my desired results!!! Thank You So Much! Been ripping my hair out trying to figure this out. Not this formula but other ones too. Couldn't figure out how to add to the formula to allow for additional Sheet Tab's to be included in a SEARCH.
@@JoshuaSmith-h8z glad you got it to work! 🙌🏼
@@sheetsbyolan You got it to work, I just plagiarized your work! Thank You!
@@sheetsbyolan One more question, is there a way to add an IF(ISBLANK) statement to put in that formula so that when the search field is blank it won't pull the whole data set? Once again Thank You in advance!!! Appreciate it!
There’s a full tutorial linked at the end of the video if you want to build it from scratch or there’s a link in my bio to buy the prebuilt template from my shop
There’s a full tutorial linked at the end of the video if you want to build it from scratch or there’s a link in my bio to buy the prebuilt template from my shop
Hi! Can you please help me? recurring tasks once marked as done are completely removed from the tasklist tab. Thank you!
Do you have the priority dropdown marked as "Recurring" or just the the Type dropdown?
Hi! Great video! But I can’t get the calendar to work. I did the exact steps, but there is no pop up calendar..help!
@@shahanazrahman4545 hey! glad you liked the video. Are you using Google sheets or excel? If google sheets you can click on the range then go to data-> data validation-> add rule-> is valid date. Once added you’ll be able to double click on the cell to add the popout calendar
@@sheetsbyolan I’m using Google sheets. It doesn’t seem to work when I do it on my Samsung Ultra tab. But I opened up the file on my laptop and not only does it work, it looks like your screen so it’s much easier to follow your tutorial. The Samsung app version is simplified. Thank you for your reply! I’m actually on the graph stage now, so nearly done, and I’m super happy with it! Thank you so much!!!
@@shahanazrahman4545 ah! That makes sense. Not all of the features seem available in the mobile edition. Glad you got it to work!
Honestly love arrayformualas
Greetings, anyway to auto sort as well the prioritizations?
Looks great - don’t suppose you can you share the sheet?
@@DeanB100AU there’s a full tutorial in the video if you’d like to build it or there’s a link in my bio for the template
Can I get copies of these?
@@mkazigwa3783 you can build the tracker using this tutorial or there’s a link in my bio for the template 😄
What are you planning for the summer?