Writing an automated Training Program for clients

B

barnymoore

New Member
Joined
Jan 19, 2023
Messages
2
Office Version
  1. 2019
Platform
  1. MacOS
Hi all,

Explanation - I have created groups in {NAME MANAGER}. The x axis (ProgramNames) and the table of content called (ProgramInfo). I have entered these into a formula on the graph so when I enter a Program Name into the 'SetSystem' column the corresponding row will change depending on the program I want.

Problem - Sometimes this works and the correct data shows, sometimes the data from another program shows and sometimes the graph does not change.

Solution - I'm wondering is my grouped name manager the problem? Is my graph data the problem? The size of the graph since this is a fraction of the 300 row graph that the data is extrapolated from? I want to be able to select a Setsystem program name and the row change to the exact data week on week.

An even better solution which I'm not to sure how to even explain is how would I be able to select a program name and the whole programs data transfers in rather than me going row by row? Do I have to create a group in NAME MANAGER first. Then how would I formulate that? Sorry if badly written. I'm not an excel dude but spent months and months trying to figure this stuff out?

2DPW (UL).xlsx
BCDEFGHIJKLMNO
3What are your projected 1RM's for the following lifts?
4Main LiftsESTIMATED 1RMReps (<12)Test Weight
51ChestDumbbell Decline Press50840
62ShouldersStiff Leg Deadlift1053100
73ChestDumbbell Flat Press1207100
84ShouldersStanding Dumbbell Press137.511100
9Day 1 (Lower)Week 1Week 2
10Set SystemOrderBody PartExerciseSetsRepsRestTempoWeightSetsRepsRestTempoWeight
11IVYSAUR 4-4-8 (Bench) (D1, 1)1ChestDumbbell Decline Press46/ 6+003055/ 5+0027.5
12SHIEKO Beginner (Deadlift) (D1,1)412,15,15,201-2 Min020
13WENDLER 5/3/1 (D1,3)412,15,15,201-2 Min027.5
14WENDLER 5/3/1 (D1,4)412,15,15,201-2 Min032.5
15WENDLER 5/3/1 (D1,5)412,15,15,201-2 Min017.5
16         
17
1816W 2X GZCL (D1, 1)2ChestDumbbell Flat Press16RM2-3 Min1    
19WENDLER 5/3/1 (D1,2)    
20WENDLER 5/3/1 (D1,3)    
21WENDLER 5/3/1 (D1,4)    
22WENDLER 5/3/1 (D1,5)    
23    
24    
25HYPERTHROPHY (4S, 30R)3ChestBodyweight Push Ups         
26HYPERTHROPHY (4S, 15,12,12,10R)4AdductorsStatic Horse Stance Squat (Dumbbell)         
27HYPERTHROPHY (4S, 12,10,10,8R)5AbductorsBanded Seat Abduction 46/ 6+000    
28HYPERTHROPHY (4S, 12,10,10,8R)6GlutesHip Thrust Single Leg (Dumbbell)46/ 6+000    
29HYPERTHROPHY (4S, 12,10,10,8R)7BackHingeHex Bar Deadlift46/ 6+000    
30HYPERTHROPHY (4S, 12,10,10,8R)846/ 6+000    
Program
Cell Formulas
RangeFormula
F5:F8F5=MROUND(J5/(1.0278-(0.0278*H5)),2.5)
D11:E11D11=D5
F27:F30,I18,F18,F11:F16F11=IFERROR(INDEX(ProgramInfo,MATCH($B11,ProgramNames),1),"")
G27:G30,G18,G11:G16G11=IFERROR(INDEX(ProgramInfo,MATCH($B11,ProgramNames),2),"")
H27:H30,H18,H11:H16H11=IFERROR(INDEX(ProgramInfo,MATCH($B11,ProgramNames),3),"")
I27:I30,I11:I16I11=IFERROR(INDEX(ProgramInfo,MATCH($B11,ProgramNames),4),"")
J27:J30,J11:J16J11=IFERROR(MROUND(INDEX(ProgramInfo,MATCH($B11,ProgramNames,0),5)*$J$5,2.5),"")
K11K11=IFERROR(INDEX(ProgramInfo,MATCH($B11,ProgramNames),6),"")
L11L11=IFERROR(INDEX(ProgramInfo,MATCH($B11,ProgramNames),7),"")
M11M11=IFERROR(INDEX(ProgramInfo,MATCH($B11,ProgramNames),8),"")
N11N11=IFERROR(INDEX(ProgramInfo,MATCH($B11,ProgramNames),9),"")
O11O11=IFERROR(MROUND(INDEX(ProgramInfo,MATCH($B11,ProgramNames,0),10)*$J$5,2.5),"")
K16,K18:K30K16=IFERROR(INDEX(SetSystemDetails,MATCH($B16,ProgramNames),6),"")
L16,L18:L30L16=IFERROR(INDEX(SetSystemDetails,MATCH($B16,ProgramNames),7),"")
M16,M18:M30M16=IFERROR(INDEX(SetSystemDetails,MATCH($B16,ProgramNames),8),"")
O16,O18:O30O16=IFERROR(MROUND(INDEX(SetSystemDetails,MATCH($B16,ProgramNames),10)*$J$5,2.5),"")
D18:E18D18=D7
F25F25=IFERROR(INDEX(ProgramInfo,MATCH($B25,HyperthrophyNames),1),"")
G25,F26:J26G25=IFERROR(INDEX(ProgramInfo,MATCH($B25,HyperthrophyNames),2),"")
H25H25=IFERROR(INDEX(ProgramInfo,MATCH($B25,HyperthrophyNames),3),"")
I25I25=IFERROR(INDEX(ProgramInfo,MATCH($B25,HyperthrophyNames),4),"")
J25J25=IFERROR(INDEX(ProgramInfo,MATCH($B25,HyperthrophyNames),5),"")
Named Ranges
NameRefers ToCells
ProgramInfo='Primary Set System'!$C$3:$CD$291F25:J30, F11:J16, F18:I18, K11:O11
ProgramNames='Primary Set System'!$A$3:$A$291K18:M30, O18:O30, F27:J30, F11:J16, F18:I18, K16:M16, K11:O11, O16
'Primary Set System'!WENDLER_5_3_1='Primary Set System'!$A$48:$V$52K18:M30, O18:O30, F27:J30, F11:J16, F18:I18, K16:M16, K11:O11, O16
Cells with Data Validation
CellAllowCriteria
B11:B30List=ProgramNames
N16List=Tempo
N18:N30List=Tempo
D25:D30List='Exercise List'!$A$2:$A$16
E25:E30List=INDIRECT($D25)
D5:D8List='Exercise List'!$A$2:$A$16
E5:E8List=INDIRECT($D5)




2DPW (UL).xlsx
ABCDEFGHIJKL
1W1W2
2DaysSetsRepsRestTempoWeightSetsRepsRestTempoWeight
316W 2X GZCL (D1, 1)116RM2-3 Min 80.0%16RM2-3 Min85.0%
416W 2X GZCL (D1, 2)611 Min 80.0%611 Min80.0%
516W 2X GZCL (D2, 1)2110RM2-3 Min 70.0%110RM2-3 Min75.0%
616W 2X GZCL (D2, 2)452-3 Min 70.0%452-3 Min75.0%
7
8IVYSAUR 4-4-8 (Bench) (D1, 1)1442-3 Min 72.5%482-3 Min70.0%
9IVYSAUR 4-4-8 (Bench) (D2, 1)2482-3 Min 65.0%442-3 Min77.5%
10IVYSAUR 4-4-8 (Bench) (D3, 1)334+12-3 Min 72.5%482-3 Min70.0%
11IVYSAUR 4-4-8 (Squat) (D1, 1)1482-3 Min 65.0%482-3 Min72.5%
12IVYSAUR 4-4-8 (Squat) (D1, 2)234+12-3 Min 72.5%
13IVYSAUR 4-4-8 (Deadlift) (D1, 1)1442-3 Min 72.5%482-3 Min72.5%
14IVYSAUR 4-4-8 (Deadlift) (D2, 1)22-3 Min 34+12-3 Min75.0%
15IVYSAUR 4-4-8 (OHP) (D1, 1)1482-3 Min 65.0%442-3 Min75.0%
16IVYSAUR 4-4-8 (OHP) (D2, 1)2442-3 Min 72.5%482-3 Min76.5%
17IVYSAUR 4-4-8 (OHP) (D3, 1)3482-3 Min 65.0%34+12-3 Min75.0%
18
19SHIEKO Beginner (Squat) (D1, 1)1151-2 Min 50.0%151-2 Min50.0%
20SHIEKO Beginner (Squat) (D1, 2)152-3 Min60.0%141-2 Min60.0%
21SHIEKO Beginner (Squat) (D1, 3)442-3 Min70.0%132-3 Min70.0%
22SHIEKO Beginner (Squat) (D1, 4)2-3 Min422-3 Min80.0%
23SHIEKO Beginner (Squat) (D2, 1)2151-2 Min50.0%151-2 Min50.0%
24SHIEKO Beginner (Squat) (D2, 2)142-3 Min60.0%151-2 Min60.0%
25SHIEKO Beginner (Squat) (D2, 3)132-3 Min70.0%442-3 Min70.0%
26SHIEKO Beginner (Squat) (D2, 4)432-3 Min75.0%
27SHIEKO Beginner (Bench) (D1,1)1141-2 Min50.0%131-2 Min50.0%
28SHIEKO Beginner (Bench) (D1,2)142-3 Min60.0%131-2 Min60.0%
29SHIEKO Beginner (Bench) (D1,3)132-3 Min70.0%132-3 Min70.0%
30SHIEKO Beginner (Bench) (D1,4)432-3 Min75.0%422-3 Min75.0%
31SHIEKO Beginner (Bench) (D2,1)2151-2 Min50.0%161-2 Min50.0%
32SHIEKO Beginner (Bench) (D2,2)141-2 Min60.0%161-2 Min60.0%
33SHIEKO Beginner (Bench) (D2,3)132-3 Min70.0%462-3 Min65.0%
34SHIEKO Beginner (Bench) (D2,4)422-3 Min80.0%
35SHIEKO Beginner (Bench) (D2,5)2-3 Min
36SHIEKO Beginner (Bench) (D3,1)31-2 Min131-2 Min50.0%
37SHIEKO Beginner (Bench) (D3,2)1-2 Min131-2 Min60.0%
38SHIEKO Beginner (Bench) (D3,3)2-3 Min432-3 Min70.0%
39SHIEKO Beginner (Deadlift) (D1,1)1131-2 Min50.0%131-2 Min50.0%
40SHIEKO Beginner (Deadlift) (D1,2)131-2 Min60.0%131-2 Min60.0%
41SHIEKO Beginner (Deadlift) (D1,3)132-3 Min70.0%132-3 Min70.0%
42SHIEKO Beginner (Deadlift) (D1,4)422-3 Min75.0%422-3 Min75.0%
43SHIEKO Beginner (Deadlift) (D2,1)2131-2 Min55.0%141-2 Min55.0%
44SHIEKO Beginner (Deadlift) (D2,2)132-3 Min65.0%142-3 Min65.0%
45SHIEKO Beginner (Deadlift) (D2,3)132-3 Min75.0%442-3 Min75.0%
46SHIEKO Beginner (Deadlift) (D2,4)322-3 Min85.0%
Primary Set System
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Welcome to the MrExcel Message Board! :)

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

NEED HELP Writing an automated Training Program for clients

Hi all, Explanation - I have created groups in {NAME MANAGER}. The x axis (ProgramNames) and the table of content called (ProgramInfo). I have entered these into a formula on the graph so when I enter a Program Name into the 'SetSystem' column the corresponding row will change depending on...
www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
If you read the rule, as requested, you will see that you don't need to do that. You just need to supply any links (other than the one I already did for you).
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
List of items within a specific month
Replies
4
Views
236
Fluff
Fluff
C
  • Solved
index match excel formula to vba code
Replies
9
Views
365
DanteAmor
DanteAmor
B
  • Solved
Array Returning "0" value
Replies
5
Views
443
Fluff
Fluff
J
  • Solved
populating dates of the current month with a formula
Replies
6
Views
356
jbrown021286
J
gtd526
  • Question Question
search and answer only ODD rows
Replies
6
Views
401
gtd526
gtd526

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top