Workout Schedule Help

[dtp81390]

Hello,

I am in the process of developing a new [FONT=inherit !important][FONT=inherit !important]workout [/FONT][FONT=inherit !important]routine[/FONT][/FONT][FONT=inherit !important] for the new year and I need some help to automate the process. Bear with me on the wording, This is a bit beyond what I typically do in excel.[/FONT]

[FONT=inherit !important]I have access to a number of predefined [/FONT][FONT=inherit !important][FONT=inherit !important]workout [/FONT][FONT=inherit !important]programs[/FONT][/FONT][FONT=inherit !important], my problem is that they get boring real quick and I need some variety in my [/FONT][FONT=inherit !important][FONT=inherit !important]workout[/FONT][/FONT][FONT=inherit !important].[/FONT]

[FONT=inherit !important]Trying to create a random priority based selection of workouts with automation.[/FONT]

[FONT=inherit !important]Set Up:[/FONT]
[FONT=inherit !important]1. 71 workout programs or variations of a program[/FONT]
[FONT=inherit !important]2. 1 sheet for each workout program with the calendar[/FONT]
[FONT=inherit !important]3. 1 schedule sheet which lists the actual program and the day number[/FONT]
[FONT=inherit !important]4. 1 weekly schedule sheet which lists the specific workout, length, [/FONT][FONT=inherit !important][FONT=inherit !important]equipment[/FONT][/FONT][FONT=inherit !important], etc.
[/FONT]5. 1 sheet for points to calculate priorities
6. 1 sheet for the priority calculation. Having issues with this sheet see below.

[FONT=inherit !important]What I want it to accomplish:[/FONT]
[FONT=inherit !important]1. I want to be able to prioritize the workouts based on availability to me, (some of them have not been released yet but I have them in here because they will be available to me when they are released), Type of workout, and any other criteria. (I have done this before for a ToDo list while I was in [FONT=inherit !important]school[/FONT] so I am pretty sure I can accomplish this task[/FONT]
[FONT=inherit !important]2. I want excel to select a workout, a weighted random, where the higher priority workouts show more frequently. Meaning prioirty 1 is scheduled twice as much as Priority 2 which is scheduled twice as much Priority 3, etc.[/FONT]
[FONT=inherit !important]3. When the program is selected from step 2 it says "Program X: Day 1" in the schedule sheet. The next time it is schedule it needs to read "Program X: Day 2". I want this function to ignore [/FONT][FONT=inherit !important][FONT=inherit !important]rest [/FONT][FONT=inherit !important]days[/FONT][/FONT][FONT=inherit !important].[/FONT]
[FONT=inherit !important]4. In the weekly schedule I want the "Program X: Day 1" to say the [/FONT][FONT=inherit !important][FONT=inherit !important]actual [/FONT][FONT=inherit !important]workout[/FONT][/FONT][FONT=inherit !important] "Core" etc. This is based off of the workout in the calendar for each of these programs.[/FONT]
[FONT=inherit !important]5. when the actual workout is entered I want the Length, and equipment to automatically fill in.[/FONT]
[FONT=inherit !important]6. I need to plan in a rest day so it is 6 days on and a [/FONT][FONT=inherit !important][FONT=inherit !important]day [/FONT][FONT=inherit !important]of [/FONT][FONT=inherit !important]Rest[/FONT][/FONT]
[FONT=inherit !important]7. Conditional formatting to cross off the workout after it is completed[/FONT]
[FONT=inherit !important]8. When the workout program is complete meaning that the number of days that program is 30, 60, 90 etc. the program is no longer scheduled[/FONT]

[FONT=inherit !important]I am familiar with some these advanced functions but not sure how to go about the process of this.[/FONT]

[FONT=inherit !important]Some of the workouts are proprietary and cannot be shared so I cannot Post the exact workbook. I may be able to develop something a little later that I can share to help with this issue.[/FONT]

[FONT=inherit !important]Any comments, questions, assistance is greatly appreciated[/FONT]

 

[dtp81390]

Update:

I created 2 new sheets based on Step 1 of what I want to accomplish:
1. A points sheet that sets the criteria for the priority calculation. It has 4 categories (Ownership, Length of Program, Type of Program, Difficulty of Program), with their sub categories with points associated with those sub-categories. Each category is selected and named for use in VLookup formula

2. Priorities page lists all of the workouts with a column for (Priority, Workout Name, Ownership, Length, Type, and Difficulty). A Data Validation list is set for each of the last 4 columns so that only the values on the points sheet can be selected. The priority column as a formula that uses the Vlookup to retrieve the associated point values for the last 4 columns and add them together to create a priority value.

Issues I am having:
I try to sort this data by priority and then alphabetically by workout and it does not sort properly, the values get mixed up. I also want to conditionally format where if the Ownership=No then all 6 columns turn a color.

Next Step:
The next thing to accomplish would be to develop a formula that would pick a workout program based on a weighted priority where the highest priority workout is scheduled twice as often as the second highest priority, etc. with workouts that have the same priority being scheduled the same amount.