Forum Discussion
PLEASE HELP!!! How do I track data without formulas?
Hello. I have created an advanced Excel Workbook for tracking client's workouts. I can input exercises and the data, such as the weights you lifted and the reps you did. HERE'S THE PROBLEM WITH IT. There are 7 KEY EXERCISES that need to be track-able. I can not use formulas to track data for those 7 key exercises because formulas need to be attached to specific cells (such as cell A1). But I don't know which cells those 7 key exercises will appear in, because it constantly changes. It might be cell A8 and the next day it might be cell A20. So instead of attaching formulas like MAX FORMULA or AVERAGE FORMULA to specific cells, I need to attach those formulas to the exercise names themselves. That way, the formulas will follow those 7 key exercises no matter which cell they end up in. To do this, I probably need to make those 7 key exercises into MACROS and then attach the formulas to the MACROS. Correct? Or how can I do this?
This is what the Workbook looks like...
The 7 key exercises can appear anywhere in any cell...
These are the 7 key exercises...
And the data from those 7 key exercises gets pulled into the "Weekly Results Sheet" here...
9 Replies
You can probably use a combination of INDEX and MATCH.
Let's say that A2 on the Weekly Results sheet contains SQUAT.
The row within A5:A16 on the Workouts sheet containing SQUAT is returned by
MATCH(A2, Workouts!$A$5:$A$16, 0)
The corresponding value in for example F5:F16 is
=INDEX(Workouts!$F$5:$F$16, MATCH(A2, Workouts!$A$5:$A$16, 0))
See if you can take it from there.
HansVogelaar Are you saying the MATCH and INDEX Formulas are able to look-up the exercise, no matter which row it appears in under Column A? So if squats appears in cell A4 or cell A16, the MATCH and INDEX Formula will still find the squats as long as it's under Column A? But in order to do that, I would have to put the MATCH and INDEX Formulas into every single cell under Column A, right?
So I'd have to create a MATCH and INDEX Formula for each of the 7 key exercises (squats, bench, deadlift, rdl, hip thrust, ceiling row, wide-grip lat pulldown). And they would all have to be under Column A because that's where the exercises will appear. But am I able to create 7 versions of the same formula for 7 different exercises, all under the same column (column A)?
You only need to create the formula in one cell, then fill it down to the cells below.
For example if a simplified Workouts sheet looks like this:
Formulas on the other sheet:
I entered the formula in B2, then filled it down to B8.
Note that the exercises are listed in a completely different order on the Workouts sheet.
