0

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.

https://i.stack.imgur.com/uLYQb.png
https://i.stack.imgur.com/nkGUQ.png

enter image description here

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) enter image description here

Link to sheet

4
  • Please Share a copy of your sheet, with anonymized data, set to Anyone With The Link can Edit. Also, heads up—your question may be closed. StackExchange is designed for "one clear question, one or more clear answers." Everything you described sounds doable. How far did you get? Do you have any experience using formulas to pull data from other cells? If so and you've hit a problem, please Edit your question and add those details.
    – Joel Reid
    May 24 at 12:41
  • Same question posted on SO.
    – Joel Reid
    May 25 at 19:08
  • @JoelReid I have no experience. Sharing the link rn.
    – Brendan
    May 25 at 23:30
  • I updated your sample sheet and added an answer below with a handful of your goals set up to be filled in by formulas automatically. I did this only because I love running and because I found the technical challenge intriguing. Frankly, this is not how stackexchange works. You're expected to have first learned how to meet your own needs, then show your work and research so far, and explain in detail what solvable technical problem you then hit. In good faith, we want all question-askers to succeed, but Web Apps SE is not a team coding workshop or spreadsheet design class.
    – Joel Reid
    May 26 at 17:55

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.