How to create a linked sheet that checks a box for a day if found and inserts text from another column

Question

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.

Also, it would be helpful to have it where it shows the total number of miles per runner in a week, and miles per runner per week (like in this picture)

Link to sheet

Answer 1

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:

=UNIQUE(Runs!A2:A)

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:

=ARRAYFORMULA(TODAY()-WEEKDAY(TODAY(),3)-(7*(9-COLUMN(B1:I1))))

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,

1. TODAY()-WEEKDAY(TODAY(),3)+(COLUMN()-2)=Runs!$C:$C looks 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)
2. $A2=Runs!$A:$A looks for a run with the runner mentioned in this row's column A
3. 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.
4. The rest just forces it to return TRUE or FALSE overall, two values which are shown as a checked or unchecked box when the cell has had that formatting applied.