FAT 2 FIT WEIGHT LOSS CHALLENGE AND SUPPORT GROUP

Join

Captain's Lounge

lauren_989
lauren_989 Posts: 2,486 Member
in Social Groups
Untitled.jpg

Happy May!

Replies

  • frankwbrown
    frankwbrown Posts: 13,894 Member
    edited May 3

    Being new to the role of captain, this is the first time I've experienced transitioning from one months spreadsheet to the next. I realized that I'd need to update not only the last weeks weigh-in in April for some folks, but the starting weight in May. Being averse to repetition, and always looking for opportunities to learn more Google sheet functions, I created a spreadsheet of my own that imported data from the April and May spreadsheets. I asked @jessicakrall8 to take a look at it and let me know what she thought. She suggested posting it here for feedback.

    The idea is, postpone updating the starting weight in the new month until all or most of the ending weights in the old month are known. Then it would be a simple matter to copy from one to the other. Now, to do this correctly, one would need to ensure the row order in both sheets are the same. They might not be if any of these things happen:

    1. the order of rows might change (e.g. someone changes their weigh-in day)
    2. new rows for new people might be added
    3. some other misc change that affects row order

    So, my spreadsheet does the following:

    1. import membernames, weigh-in day and ending weight from the old months sheet
    2. import membernames, weigh-in day and starting weight from the new months sheet
    3. compare row by row and flag any row that has conflicting values between old and new

    Of course, my code was specific to the Shape Shifters tab. So I just parameterized it and created a tab for each team. You can see the effect of changes on the tabs for DOWNSIZERS and WEIGHT NO MORE. This could be avoided by adding new members at the bottom until all weights are copied forward, then sorting the rows to put those new members where they should be.

    It occurs to me that it should be possible to match up names so that the row order wouldn't matter. But that depends on whether you're even in favor of this approach.

    Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1pf1MeiQON9diQCNKVIrDWPwIQDb5y0y-eDO1zGW_0z4/view

  • lauren_989
    lauren_989 Posts: 2,486 Member

    @frankwbrown I typical copy the weights from April to May, so you just put it in April like normal and I take care of moving it to May.

    That being said, is your spreadsheet easier? I usually just copy all the weights on the day I make the spreadsheet, then copy paste the rest on Sunday morning when I do the tallies.

  • jessicakrall8
    jessicakrall8 Posts: 5,627 Member

    Captains please help to promote:

    The May Week 1 Group Challenge is live and ready to begin on Sunday, May 4th. Join us for the Move It in May Challenge!! Get a good 4 of 7 days of activity and post your adventures! Here's your link:

    May Week 1 Group Challenge - Move It in May — MyFitnessPal.com

    Looking forward to lively discussions! See you in the chat thread!

    Jessica

  • frankwbrown
    frankwbrown Posts: 13,894 Member

    @lauren_989 My spreadsheet had errors due to my failing to parameterize several formulas. I fixed that.

    I erroneously made the assumption that each team is responsible for copying weights from end of one month to beginning of the next. I understand now that this is your roll. What you describe is in fact what I thought would be the simplest, regardless of who does it. It's also why I created the spreadsheet.

    So, regardless of who does it, I do think my spreadsheet makes it easier. To demonstrate, take a look at the sheets for DOWNSIZERS and WEIGHT NO MORE. In both cases, the addition of new members throws off the corresponding rows between the April and May sheets. This would interfere with a simple copy/paste from April to May.

    So, initially, my spreadsheet simply flagged where the corresponding names don't match. But this morning, I went a step further and used the function VLOOKUP to match each April ending weight to the corresponding member name from May. This would allow you to copy the values in I6:I40 (April ending weight) in this spreadsheet and paste them into D6:D40 (May starting weight) in the May spreadsheet. This would work even when the member list ordering differs in May and April (as is the case with DOWNSIZERS and WEIGHT NO MORE). To see what I mean, look at tabs WEIGHT NO MORE (revised) and SHAPE SHIFTERS (revised).

    Does this make any better sense now?

Categories