CASE STUDY 7

Google Sheets - SCHEDULE TEMPLATE

How to remove the used options from a Dropdown Menu

How to remove the used options from a Dropdown Menu

Google Sheets is used by many in building all kinds of tables, easy or complex calculations for business but also for personal use. 

When using a dropdown menu you are generally looking for data consistency. You do not want to have different ways of writing your data as you might want to be able to filter your table, create summaries, or maybe graphs, and having 2 columns reading Flowers and Fowers will be a pity.

But now, what if you want to make sure that you are only using ONCE the options you select in the dropdown menu. Let's say that in the morning you have a list of activities that you need to do until the end of the day. If the list contains 2 - 3 items is not hard to follow them one by one. But what if you need to organize 20, 30, 100 actions for the week? Following up on what job was done and that not could slow you down.

Let's see if we can build a small scheduler in Google Sheets. We will have different actions that can be done by the team using various pieces of equipment. 

As this is not a real Workload Scheduler but more a case study to show how to remove the used options from a Dropdown Menu here is the limited setup: we have a printer, a laminator and a cutter, 2 employees and 4 jobs for the day:

  • Print the Coca Cola campaign (est 2 h)
  • Laminate the Coca Cola campaign (est 0.5 h)
  • Print the Pepsi campaign (est 2.5 h)
  • Contour Cut the Pepsi campaign (est 3 h)

So, how to make sure that your file will only show the unused time slots on the dropdown menu?

Make sure you are registered to see the answer.

Registered members have FULL ACCESS to the content. REGISTERING IS FREE!

In this file, the main functions used are: IF, IFERROR, VLOOKUP, FILTER

Mark when a Job is Picked from the Remaining Task List:

=IF(IFERROR(VLOOKUP(C23,$C$12:$C$19,1,0),)=””,FALSE,TRUE)

  • Let’s break this formula into steps. from inside out:
  • Check if any of the Jobs that need to be done (Remaining Tasks) was already scheduled: VLOOKUP(C23,$C$12:$C$19,1,0) – this will give an error for all the jobs you have on the Remaining Task and were not picked in the Scheduled Table.
  • We clear the ERROR with the IFERROR function so the IFERROR(VLOOKUP) will return an empty (“”) result only if the Task was not yet Scheduled.
  • With the IF() function we will identify all the cases where the Task was Schedules and we instruct the Checkbox to get a Check Mark.

Dynamic Dropdown Menu:

=FILTER(Agenda!C23:C32,Agenda!F23:F32=FALSE)

  • On the Sheet [Variables] we are entering the base for the dropdown menu on the Scheduled Jobs table.
  • Since every time a task is picked, the Column F (PICKED) will get the TRUE value (the checkbox will get the checkmark),
  • We are filtering the results so that we will only show the NON-PICKED Jobs from the Daily Tasks

Data validation:

Close the exercise by adding the Data Validation for the section C12:C15 to select the values from the sheet [Variables]: the Daily Tasks not yet picked.

This content is locked

Login To Unlock The Content!