Sunday, May 6, 2012

yogi_Compute Stats For Weekly Weight Loss By User Biggest Loser Style

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #530    May 6, 2012

user DaninSA said:
How do I set a cell to change it's formula based on data in the row?
My question may not be phrased correctly, so let me explain what I'm trying to do.  I am maintaining a spreadsheet for a group of friends involved in a weight loss program. It is set up with columns for initial weigh in, then weekly weigh ins.  I have formulas based on the last entered weigh in data, but I have to manually edit those formulas as the users enter the data.
What I would like to do is put a formula or script into the column "Last Weigh In" that would automatically pull in the latest weigh in data when it is entered, essentially updating the formula from "=E3" to "=F3" when the user enters data in column F.
For my next trick, after the weigh in data is entered and the formula updated, I would like it to grab a pre-defined range and sort it, instead of having to do it manually.  I'm still browsing through the script gallery to find this one, so if someone can point me to it I'd appreciate it.
Link to a personal info stripped copy of the spreadsheet:
my fellow contributor MarinusP had made an excellent contribution in regard to the main formula for this problem in Google Docs Help forum -- that inspired me to post the following  complete solution to the problem