Creating an Auto-Updating Library in Excel

[SuperRedRobin92]

Hi All,

Im currently working on an exercise library for my own personal training business.
The library consists of an "Exercise Index" sheet listing all exercises, then 4 subsequent sheets that are labelled "Push Systems", "Pull Systems", "Legs Systems", "Core Systems"

The first row of each sheet contains the following headings, "Name", "System", "Major Muscle", "Secondary Muscles", "Regression", "Progression" "Alt Names"

What i am looking to do is fill in the exercise data on the index sheet, then have the workbook automatically update, and copy that information to the correct sheet.

So, for example,

Name	System	Major Muscles	Secondary Muscle	Regression	Progression	Alt.Names
Pushup	Push	Chest	Triceps, Shoulders	On Knees	One Arm	Press Up
Pullup	Pull	Lats	Biceps, Shoulders	Assisted	Weighted	-
Squats	Legs	Quads	Hamstrings, Glutes	Sit to Stand	Jumping	-

<tbody>
</tbody>

How would i copy the above data into their respective Sheets?
I understand it can be done with macros, but from my understanding a macro needs to be run everytime the desired outcome is to be realised. is there anyway to automate the process?

Sorry for the long question! Any help would be greatly appreciated!

Thanks!

 

[Eric W]

One possibility is to put a WorkSheet_Change event on your Index sheet. It is VBA code, but it kicks off automatically whenever you change something on that sheet, so potentially you'll never know it's doing anything, and you don't have to remember to run it.

What it could do is check the row number of the cell you changed. Given that, it looks up the values in the name and system column. Using those, it goes to the appropriate sheet and looks to see if the name is there. If not, it adds it. If so, it updates the other fields (Major Muscles, Secondary Muscles, etc.) to match what's on the Index sheet.

If that's something that sounds like it might work, let me know and I'll write something up.

 

[SuperRedRobin92]

Thanks for the response Eric!

That Sounds like it could work! I dont have any experience with VBA so your help would be greatly appreciated!

Thanks!

 

[Eric W]

This is fairly simple, but since you're new to VBA, it might look complicated, but don't worry.

Open your spreadsheet. Press Alt-F11 to open the VBA editor. On the left you'll see a navigation pane with all your sheet names. Double-click on the Index Sheet name. This will open up a window. Copy the below code and paste it into this window:

Private Sub Worksheet_Change(ByVal target As Range)

Dim FirstCol As Integer, LastCol As Integer, FirstRow As Integer
Dim ExerciseCol As Integer, SystemCol As Integer
Dim Syst As String, i As Integer, j As Integer
Dim SystemSheet As String, ExerciseName As String
Dim System As Variant, SystemSheets As Variant

    FirstCol = 1
    LastCol = 7
    FirstRow = 2
    ExerciseCol = 1
    SystemCol = 2
    
    System = Array("push", "pull", "legs", "core")
    SystemSheets = Array("Push Systems", "Pull Systems", "Legs Systems", "Core Systems")
    
' If the cell is in the wrong column, then exit
    If target.Column < FirstCol Or target.Column > LastCol Then Exit Sub
    If target.Row < FirstRow Then Exit Sub
' If the selection is more than 1 cell, then exit
    If target.Cells.Count > 1 Then Exit Sub
    
' Find the Muscle system
    Syst = LCase(Cells(target.Row, SystemCol))
    If Syst = "" Then Exit Sub
    For i = 0 To UBound(System)
        If InStr(Syst, System(i)) > 0 Then GoTo GotOne:
    Next i
    Exit Sub

' Found the right sheet
GotOne:
    SystemSheet = SystemSheets(i)
    ExerciseName = Cells(target.Row, ExerciseCol)
    
'Find the matching exercise on the other sheet, or a blank row
    i = FirstRow
    While Sheets(SystemSheet).Cells(i, ExerciseCol) <> ExerciseName And _
          Sheets(SystemSheet).Cells(i, ExerciseCol) <> ""
        i = i + 1
    Wend
    
' Update the fields
    For j = FirstCol To LastCol
        Sheets(SystemSheet).Cells(i, j) = Cells(target.Row, j)
    Next j
    
End Sub

You didn't specify exactly which rows and columns your table has. I assumed A-G, with a header in row 1, data starting in row 2. If that's not true, it's easy enough for you to change by changing the FirstCol, LastCol, etc. fields to whatever they should be. This also assumes that the tables on the other sheets are in the same position.

The array with the words (push, pull, legs, core) has the words you need to put in the System column, and the line right below says what sheet to use for that word.

That's about it. Go back to Excel and try typing in a few. Experiment with new lines, updating old lines, etc.

Good luck!

 

[SuperRedRobin92]

Thanks Eric!

This worked exactly as you described it! Very Much appreciated!

Thanks again!

 

[Eric W]

Glad to help, thanks for the update!