Captain's Lounge
Happy May!
Replies
-
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:
- the order of rows might change (e.g. someone changes their weigh-in day)
- new rows for new people might be added
- some other misc change that affects row order
So, my spreadsheet does the following:
- import membernames, weigh-in day and ending weight from the old months sheet
- import membernames, weigh-in day and starting weight from the new months sheet
- 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:
0 -
@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.
0 -
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
0 -
@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?
0 -
@lauren_989 Sorry for the slow reply! You asked if I wanted to stay on the Shape Shifter team. I think it would be fine to keep me on support for now. I don't see myself leaving forever and hope to be back in the future. I just need a little bit of a break. Thank you!
2 -
@lauren_989 Hi there! On Mission Slimpossibles Digger61 weighed in late so I changed it from 213 to 211 and the NWI will need to be removed. Thank you!
1 -
Captains please help to promote:
The May Week 3 Group Challenge is open and ready to begin on Sunday, May 18th. Please join us for the "Get to the Core of It" Challenge! This is an opportunity to focus on the core of your needs for the week and share with the group so we can all celebrate personal successes. Here's your link:
May Week 3 Group Challenge - Get to the Core of It! — MyFitnessPal.com
See you in the chat thread! 🍎
Jessica
0 -
Hello!
I have questions about maintaining the spreadsheet. I know there's a lot of referencing among the various tabs that relies on each group's sheet adhering to a specific (common) format. And of course, I realize the layout changes depending on whether there are 4 or 5 weeks for the given month. What I would like to do is use areas outside that common format to do things like the following:
- search a given week's weigh-in's to list the members who are late with their weigh-in
- search a given week's steps to list members who have not reported steps for certain days
- compute for a given week that week's step totals with rankings, averages, etc.
I can do the above in a separate spreadsheet, importing the needed data, but it would be convenient to have an area on my group's sheet in which to work (a workspace, as it were).
To see what I mean, refer to the Shape Shifter tab AP4:AQ23.
- cell AP5 is the week number I want to look at: 1 thru 5
- cell AQ5 uses the choose function to convert the week number to it's corresponding column name
- columns AQ6:AQ23 contain a formula to list the member name if they have no weigh-in that week
- you can easily toggle thru the weeks by changing the value in cell AP5 (feel free to do so).
This code is rather crude. I'd like to be able to polish it up a bit, as well as add other code, sometimes only temporarily (like a scratch pad).
What do you think?
0 -
Hi @lauren_989 ! It looks like you already handled it on the spreadsheet but DrewsAnna from Mission Slimpossibles is in the hospital and has asked to skip this week's weigh in. Thank you!
1 -
@frankwbrown if you want to make your own spreadsheet for steps for your team, that is fine. Mission Slimpossible does that and it works well for them!
If you want to use space off to the side of the spreadsheet, that is fine as long as it doesn't interfere with the actual spreadsheet where the original layout is (like using the AP columns like you did is fine).
0 -
@lauren_989 Great!
Would you mind if I defined a few global constants and a few functions? I don't see that we have any currently.
I created a function (elsewhere) that I think would be quite useful in generating column headings, e.g.:
- assume we've defined a constant startDate as 5/4/25
- invoking a function weekHeading(startDate, 1) generates "Week 1: May 4 - May 10"
- invoking weekHeading(startDate, 2) generates "Week 2: May 11 - May 17"
- invoking weekHeading(startDate, 3) generates "Week 3: May 18 - May 24"
- day headings like "Sun 5/4", "Mon 5/5",… "Sun 5/25",… Sat 5/31" could be similarly generated
- changing startDate to 6/1/25 in the June spreadsheet would automatically generate all these day/week headings
How about I make some changes for Shape Shifters to show what I mean. If you don't like it, I can change it back easily enough.
1 -
So using the formula, would it be:
="Week 1: "&text($E3,"mmm d")&" - "&text($E3+6,"mmm d") if I put the start date in cell E3?
And then the other formulas in the other boxes?
0 -
Yes, that would populate the heading for week 1. You can make it easier to understand by naming cell E3 something like startDate:
"week 1:" & "&text(startDate,"mmm d")&" - "&text(startDate+6,"mmm d")You could populate all weeks by incorporating the column number in the formula:
The week 1 heading is in column F, and four columns to the right is each following week in turn.
week 1 = column F = column number 6. — you can confirm that with =column()
week 2 = column J = column number 10
week 3 = column 14; week 4 = column 18; and week 5 (when there is one) = column 22So, to get the week# from the column#, we can do this: int(column()/4)
int(6/4) = 1; int(10/4) = 2; int(14/4) = 3; int(18/4) = 4; int(22/4) = 5
This works for week 1:
"week " & int(column()/4) & ":" & text(startDate,"mmm d")&" - "&text(startDate+6,"mmm d")But for the other weeks, we need to add a multiple of 7 to the dates:
(week - 1) * 7:
week 1 = (1 - 1) * 7 = 0; week 2 = (2 - 1) * 7 = 7; week 3 = (3 - 1) * 7 =14so, we have:
"week " & int(column()/4) & ":"
& text(startDate + (int(column()/4)-1)*7,"mmm d")
&" - "&text(startDate + (int(column()/4)-1)*7 + 6,"mmm d")That works, but it's pretty ugly.
But we can hide all that by defining a function that we pass the week# to:
Let's say function week_heading takes one parameter (week) and uses the named cell startDate,
and is defined as:
="Week " & week & ": " & text(startDate+(week-1)*7,"mmm d") & " - " & text(startDate+(week-1)*7+6,"mmm d")Then you just put
=week_heading( int(column()/4) )
in cells F3, J3, N3, R3 and V3 (if there's a fifth week)Or, move the int( __/4) to the function and just say
=week_heading( column() )Or, just make each week# a literal:
cell F3 =week_heading(1)
cell J3 =week_heading(2)
cell N3 =week_heading(3)
cell R3 =week_heading(4)
cell V3 =week_heading(5)0 -
@frankwbrown My head hurts just reading that…bet you're a beast with those wretched VLOOKUPS, aren't ya?! UGH…they kill me every time, even when someone takes time to try and explain them. I'm jealous…carry on! 😑
0 -
Captains, please help to promote:
The May Week 4 Group Challenge is open and ready to begin on Sunday, May 25th. Please join us for the Walk, Run, MOVE challenge! Set your pace and pick your place, but let's all get moving! Here's your link:
May Week 4 Group Challenge - Walk, Run, MOVE! — MyFitnessPal.com
See you in the chat thread this week…
Jessica 😎
0 -
@lauren_989 Nancyinmiami/ss Motivator is going to start a book club and open it to members of all five teams. I will help her to set up the announcement and post in all of the team chats. First book is one on nutrition/weight loss journey but we will likely choose different reading genres each month. Will keep that announcement chat open indefinitely. Just wanted to get your blessing first. We thought it would help to generate more discussions for the teams. Please advise. Thanks!
Jessica
1 -
@jessicakrall8 that sounds like a great idea! I love it and would likely join in the conversation!
1 -
@lauren_989 Announcements just went out:
Please join us and your Motivator, @nancyinmiami for our new Book Club! Here's your link:
Page Turners & Portion Burners Book Club — MyFitnessPal.com
More information and great discussions to follow. Invite your friends…open to all Fat2Fit members on all five teams!
Jessica
0 -
I need a link to the templates.
0 -
Never mind! (I found the emails!)
0 -
@jessicakrall8 Could you do me a favor?
Could you go to the F2F May 2025 spreadsheet and try to change the value of week to 1 or 2 or 3, etc. in cell 'SHAPE SHIFTERS'!AZ49? I'd testing a feature I'm unfamiliar with.
0 -
@lauren_989 I'm largely done with the 4 week and 5 week templates. Please review and give me feedback.
0 -
@frankwbrown awesome! I will look at them as soon as possible.
0 -
@jessicakrall8 Could you do me a favor?
Could you go to the F2F May 2025 spreadsheet and try to change the value of
weekto 1 or 2 or 3, etc. in cell'SHAPE SHIFTERS'!AZ49? I'd testing a feature I'm unfamiliar with.I changed that cell to 3 and 4 and each time it changes all of the values in the AY cells for all of the cells within that column block…take a look. I think I left it as 3.
Jessica
0 -
@jessicakrall8 Okay good.
Those cells in column AY are protected but are their values are generated based on the value in cell AZ49. I just wanted to confirm that the values in those AY cells can change even when the cell itself is protected. (the goal being to avoid inadvertent changes to those cellls.)
Thanks.
0 -
I am unable to complete changes to the DOWNSIZER tab in the new template due to a protection rule. Please remove or edit that rule and let me know. Thanks.
0 -
Frank - I have no idea how I added a protection rule or how to remove it. Lol. I am going to email you my phone number. Please feel free to text or call me!
0 -
Good Evening everyone!
As you have probably seen from some of the chats, Frank (@frankwbrown) has been helping us update our spreadsheet to make it easier to copy each month and for some of the tallying.
There will now be only 1 spreadsheet for each month whether it be a 4 or 5 week month. If it's only 4 weeks (like June) the last column will be grayed out and you won't be able to enter anything into those cells.
Most of it is the same, you will just enter weights/steps as you have been doing. If you notice that you get an error, or something doesn't work, please reach out here! (Right now, team Downsizers has a grayed out box for week 4 for the weeks in the green - I am still trying to figure this out)
I will still copy the rest of the starting weights for everyone over to the new spreadsheet on Sunday when I do the tallies.
I appreciate all of Frank's help!
2 -
@lauren_989 — I fixed the error on Downsizers if/when there are four weeks in the green.
Everyone: I know of no issues at this time.
Please post here with a mention if you see anything strange, want some clarification, or have a question.
3 -
Well, I haven't heard anyone screaming yet, so either things are going good or people have not yet had a chance to view and/or change the spreadsheet.
I should have mentioned, if anyone has ideas on how to make administering the challenge easier as it relates to the spreadsheet, please let me know. I'm not a spreadsheet guru, but I have some good basic skills, and I'm learning more every day.
Here's a couple of ways to make things easier:
- You occasionally need to remind people that they haven't yet posted their weigh-in for the week. I created an easy way to see that for Shape Shifters. If you look on our sheet in the range AO3:AR40, you'll see that by entering the week# in cell AP4, it lists all those who have not yet posted a weight for that week (right now of course, it's everybody!). If you'd like to use it, feel free to copy/paste to your team's sheet (you'll need to select cell AO3 on your sheet before pasting, otherwise the cell references won't be correct. If you need help with this, just ask.)
- Occasionally, people change the day they want to post, or they move to support or back again, or someone new joins the team. I have a solution that re-sorts the correct range of cells after such changes without having to manually select the range and specify the sort. It should work for any team, as long as all sheets agree on the first and last rows involved. I'm still working on how to make it easily accessible.
- For those teams that do the step challenge, there's a similar need to remind people that they need to post their steps. For Shape Shifters, I can enter a week# in cell AZ50 and see who needs to be reminded and for which days.
- Think about what other things you'd like to see. Maybe we can make it happen.
1
