I'm trying to create a sheet in Google Sheets for my team that shows me what runners have run on what day of the week. Their activities are automatically imported via Zapier with dates from Strava into another worksheet. I would like to have it where when they run and it imports the activity into the sheet, in the other worksheet, it checks off the day of the week for that runner. Ideally, it would reset weekly. Pictures are attached.
I've updated your example spreadsheet with some demonstrations of how your mentioned goals could be accomplished. Some formulas I added are below.
For things like a list of names, a formula can populate a list of all entries found elsewhere, skipping blanks and listing repeats only once each:
For tracking miles week-by-week, put in a sheet's
B1, this formula will fill
B1:I1 with the last eight weeks' Monday's dates:
For ticking a grid of checkboxes based on matching list entries elsewhere, in this case for who ran on which days, each cell can use a formula resembling:
=ARRAYFORMULA(IFNA( MATCH(1, ($A2=Runs!$A:$A) * (TODAY()-WEEKDAY(TODAY(),3)+(COLUMN()-2)=Runs!$C:$C), 0)>0,FALSE))
That formula can be dragged down and across to fill whatever size grid you end up with.
From the inside out,
TODAY()-WEEKDAY(TODAY(),3)+(COLUMN()-2)=Runs!$C:$Clooks for a run on (this week's first day plus however many days we need to add based on what column Day we're in)
$A2=Runs!$A:$Alooks for a run with the runner mentioned in this row's column A
- surrounding those two parts,
(…) * (…)makes the later MATCH succeed only if the searching found a line where both "the name matches" and "the date matches" were true.
- The rest just forces it to return
FALSEoverall, two values which are shown as a checked or unchecked box when the cell has had that formatting applied.