Exclusive comparison

Trying to implement some functionality as a close up checklist for my coffee shop. So I have a list of jobs that need to be done each day (Jobs To Complete). I have another sheet, Cleanup, that stores the completed jobs. I’ve got a ref in Cleanup to JTC. What I want to see in the dropdown to select the job are the uncompleted jobs…so jobs that do not currently have an entry in Cleanup for that day. Below is the structure for each

Cleanup Jobs (TABLE):
Desc: List the jobs that are required to be done at sometime during the day
ID (int)
Job (text)
1 column for each of the 6 days we’re open as a boolean if that job is applicable on that day

Jobs for Today (SLICE):
Desc: Filter the above table to only show the jobs that are required on the current day
Filter for above table:

IF(WEEKDAY(TODAY()) = 2, [Mon],
IF(WEEKDAY(TODAY()) = 3, [Tues],
IF(WEEKDAY(TODAY()) = 4, [Wed],
IF(WEEKDAY(TODAY()) = 5, [Thur],
IF(WEEKDAY(TODAY()) = 6, [Fri],
IF(WEEKDAY(TODAY()) = 7, [Sat], FALSE))))))

Cleanup (TABLE):
Date (date)
Job (ref -> Jobs for Today)
Completed By (ref -> employee table)

Completed Jobs (SLICE):
Desc: I only want to see the jobs for today
Filter for above table:

[Date] = TODAY()

Cleanup (UI):
Data: Completed Jobs (SLICE):
Desc: Show me the jobs for today and let me add a new completed job after an employee completes it.
Type: Table (can be anything as long as I can see all completed jobs

I want a slice or filter that will allow me to only see the uncompleted jobs for the current day and that is all that would display in the Jobs reference in Cleanup UI so once they were all gone, I would know that everything is done and we can close up and leave for the day. I can refactor if that would make things easier.

The approach here is to start with the list of all jobs to be done that day, then remove from that list the jobs that have already been completed, leaving only the jobs that are not yet complete. You’ll need to use list subtraction, described in here:


Thanks, @Steve! If I change the Cleanup -> Job column to Text and use Valid If formula (Jobs for Today[ID] - Completed Jobs[Job]) it filters and gives me just the IDs of the incomplete jobs. Now I need to return the [Job] text instead of the ID. Is there a way to do that? Thanks again!

Why did you do that?

When I was trying to figure out how to implement from the link, I was trying different things and it was throwing up type casting errors. I guess I should have just added the valid if formula cuz it’s working now. Thanks!

1 Like