Google-sheets – Change COUNTIF range based on the current day of the week, with 3 days using the same dataset

google sheetsgoogle-sheets-timestampworksheet-function

I work as a delivery driver and have been futzing around trying to create a google sheet that automatically updates with the current day's drops. However, there is a rub with the data set. The publishers all use different "drop codes" denoting which day(s) a customer gets service. I have the different days drop codes on a separate sheet, same workbook (? googles naming convention is ridiculous)), in named ranges and generate the day's sheet with…

=FILTER(stats!A2:H, COUNTIF(daily , stats!G2:G))

…where 'stats' is a sheet with the entire route, column G containing the drop code for that customer, and 'daily' being the named range containing the drop codes relevant to that day. The drop code-named ranges being daily, thurs, fri, sat, and sun. Notice the rub? Monday, Tuesday, and Wednesday doesn't change so they are grouped under daily as they get service…well, daily.

The question: How would one go about changing the drop code set with the current day if 3 of the days repeat?

I realize I could do something like copy the daily column twice and have M, Tu, and Wed with identical cells or out the drop codes across rows having the call use TODAY() to fill in the row number. But part of this exercise is to learn more about the deeper workings of spreadsheets and (hopefully) not make redundant massive clunky code blocks (like my python projects…). I imagine TODAY() needs to be integrated around the drop code-named ranges, and I've poked around the TRANSPOSE formula help page, but it's not clicking how to put it all together if that formula would work for this at all.

Any insight into the syntax of getting this to function would be much appreciated.

Best Answer

OK. I went a bit above and beyond on this. Damn thing frustrated me all afternoon.

The best way I could figure to do it was to QUERY your list, using the different daily drop combinations as conditions for the QUERY. This gets past your issue of M/T/W all having the same schedule, and being part of Th-Su.

To do this, I used JOIN to create a partial "WHERE" statement for the QUERY for each set of combinations.

=join("' or G='",conv!A2:A5) = D' or G='DS' or G='D5' or G='D6.

THIS then gets added to the QUERY by referencing the cell that has the JOIN for the desired day:

=query(stats!A:G,"Select * where G='"&J3&"'"). In this example, J3 is replaced by the JOIN, providing =query(stats!A:G,"Select * where G='D' or G='DS' or G='D5' or G='D6'). This would fill down a list of all of the deliveries for a M/T/W.

But wait, there's more...

That will only get you part of the schedule. To do the other days, you would have to manually edit the QUERY every time, or have a separate page for each delivery schedule. YUCK

So, I made a drop-down of the different days of the week. This is then tied to an IFS statement within the QUERY that then selects the correct delivery combinations based on which day of the week you select.

=query(stats!A:G,"Select * " & IFs(A1="M,T,W","where G='"&J3&"'",A1="Th", "where G = '"&J4&"'",A1="F", "where G = '"&J5&"'",A1="Sa", "where G = '"&J6&"'",A1="Su", "where G = '"&J7&"'"),1)

Here is how the whole bit is constructed:

Imgur

Here are a couple of examples showing that the formula works:

Imgur Imgur

I have placed everything on a single page and copied it into your workbook. Let me know if you have any problems or questions.

Related Topic