Find lowest and the highest values across the sheets and enter into cell

Hello! I have this Excel sheet that I use to track my fitness workouts. The attached workbook has some sample data in it. Under the tab "Week 10", column A is for exercise names. Exercise logs get entered into columns B:K. More tabs are added as needed for each new week. Potentially, for weeks 1 to 53, and they are named accordingly. The following is what I wanted help with:

1. I want to have a collective list of all workouts on a separate sheet, which is the tab "Exercises". On that sheet, new exercises are added as needed. I want each of the entered exercise to display the lowest and the highest values of the weight that it can find in exercise logs on all the "Week xx" sheets. I do not know what kind of reference to the cells is needed, but I attached a text file with reference to all the cells for convenience, just in case.

2. Each low/high display of values should not display any error, in case it can?t find any values for that particular exercise.

3. On "Week xx" sheets, when I add exercises to column A, I want the high values for the exercise to be automatically entered into column B of the corresponding row so that when I begin working on that exercise, I can see the maximum weight that I have ever lifted. As I do the workout, I may or may not change that value manually to reflect the actual weight that I ended up lifting.

See if this is how you wanted.

1) To Exercises sheet code module

Private Sub Worksheet_Activate()
    Run Me.CodeName & ".worksheet_change", Me.Range("a1")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Target.Range("b1:c1").ClearContents
        test
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End Sub

2) To a standard code module

Sub test()
    Dim rng As Range, r As Range, x(1), s As String, ws As Worksheet
    With Sheets("Exercises")
        Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
    End With
    rng.Columns("b:c").ClearContents
    For Each r In rng
        For Each ws In Worksheets
            If LCase$(ws.Name) Like "week *" Then
                x(0) = Application.Match(r, ws.Columns(1), 0)
                If IsNumeric(x(0)) Then
                    s = "b" & x(0) & ":k" & x(0)
                    x(1) = ws.Evaluate("min(if((" & s & "<>"""")*(isnumber(" & s & "+0))," & s & "+0))")
                    If IsEmpty(r(, 2)) Then
                        r(, 2) = x(1)
                    Else
                        If r(, 2) > x(1) Then r(, 2) = x(1)
                    End If
                    x(1) = ws.Evaluate("max(if((" & s & "<>"""")*(isnumber(" & s & "+0))," & s & "+0))")
                    If IsEmpty(r(, 3)) Then
                        r(, 3) = x(1)
                    Else
                        If r(, 3) < x(1) Then r(, 3) = x(1)
                    End If
                End If
            End If
        Next
    Next
End Sub

Thank you, jindon. Unfortunately, it does nothing but clearing the cells in columns B:C on the Week sheet.

jindon, that one does populate the values into the cells of the Exercises sheet, but it takes the values of the Reps into account, which is not what it should. It should only take the values of Weight from the Week sheets. Unfortunately, still no action on the Week sheets, where column B suppose to auto-populate the highest weight for the entered exercise in column A. By the way, I just thought of this one other thing: I think, that auto-populated value should not be coming from the Exercises sheet, but it should rather run it's own lookup, because the Exercises sheet only updates its values when the sheet is refreshed.

Change 2 lines

                If IsNumeric(x(0)) Then
                    s = "b" & x(0) & ":k" & x(0)
                    x(1) = ws.Evaluate("min(if((" & s & "<>"""")*(isnumber(" & s & "+0))," & s & "+0))")
                    If IsEmpty(r(, 2)) Then
                        r(, 2) = x(1)
                    Else
                        If r(, 2) > x(1) Then r(, 2) = x(1)
                    End If
                    x(1) = ws.Evaluate("max(if((" & s & "<>"""")*(isnumber(" & s & "+0))," & s & "+0))")
                    If IsEmpty(r(, 3)) Then
                        r(, 3) = x(1)
                    Else
                        If r(, 3) < x(1) Then r(, 3) = x(1)
                    End If
                End If

to

                If IsNumeric(x(0)) Then
                    s = "b" & x(0) & ":k" & x(0)
                    x(1) = ws.Evaluate("min(if((" & s & "<>"""")*(mod(column(" & s & "),2)=0)*(isnumber(" & s & "+0))," & s & "+0))")
                    If IsEmpty(r(, 2)) Then
                        r(, 2) = x(1)
                    Else
                        If r(, 2) > x(1) Then r(, 2) = x(1)
                    End If
                    x(1) = ws.Evaluate("max(if((" & s & "<>"""")*(mod(column(" & s & "),2)=0)*(isnumber(" & s & "+0))," & s & "+0))")
                    If IsEmpty(r(, 3)) Then
                        r(, 3) = x(1)
                    Else
                        If r(, 3) < x(1) Then r(, 3) = x(1)
                    End If
                End If

jindon, the Week sheet still doesn't work. BTW, your attached file did not have any code for the Week sheet, so I copied it from your previous post, then changed the relevant lines per your last instruction.

If you are talking about a workbook other than you have ppost, how could I even guess?

Originally Posted by chrisneu

the Week sheet still doesn't work.

In your opening post, you stated,

Originally Posted by chrisneu

I want each of the entered exercise to display the lowest and the highest values of the weight that it can find in exercise logs on all the "Week xx" sheets.

So, you must have multiple sheets named "Week xx" and find lowest and highest across all worksheet named "Week xx".

That's what I read your problem,

Upload the workbook that is NOT WORKING with the extra sheets that is showing your expected result.

jindon, I am still referring to the same workbook, nothing else. With "Week xx", I am referring to the current sheet "Week 10" and any other "Week" sheet that I will ad later. The current week of the year is week 10. Next week will be 11th week of the year. I will add a new tab with the name "Week 11" to the workbook, and more tabs for each new week. It appears that you have missed this in the first part of my original post

More tabs are added as needed for each new week. Potentially, for weeks 1 to 53, and they are named accordingly.

Regardless, when I stated that the code does not work on the Week sheet, I referred to the current "Week 10" sheet that you have access to with the file that you have been working with.

Then the workbook should look like this.
Change the cell in col.A or select other sheet and select back Exercises sheet should run the code.

jindon, yes, the values on the sheet Exercises do appear to take just the weight values from the sheet Week 10. Unfortunately, the values are taken only from the first exercise that it finds and it does not look any further. For example: take the first exercise "Alternating dumbbell bench press" and copy the name to another cell somewhere in column A, then add, let's say "999" to column B of the same row. The value of C2 on the Exercises tab should change from "50" to "999", but it's not. Now, delete the exercise in cell A6 of Week 10 tab and check the value for that exercise on the Exercises tab - now it did replace the "50" with "999". Same problem when you add a new Week sheet. Additionally, when you duplicate the exercise "Alternating dumbbell bench press" on Week 10 sheet, column B for on that row should auto-populate with the highest weight it finds for that exercise, which is essentially the same value as C2 of the Exercises sheet.

No Worksheet Activate event anymore.

1) Double click on Col.A will update all data.
2) Add/change in Col.A will update corresponding 2 cells.
3) change in Col.B is taken as new MIN and updates other same name accordingly if any.
4) change in Col.C is taken as new Max and updates other same name accordingly if any.

What I'm referring is this change

Originally Posted by chrisneu

For example: take the first exercise "Alternating dumbbell bench press" and copy the name to another cell somewhere in column A, then add, let's say "999" to column B of the same row. The value of C2 on the Exercises tab should change from "50" to "999"

The workbook now is applying this change,
It is not from all the Week xx sheet, just on Exercise sheet.
So, when you add new Week xx sheet, it must be recalculate from all the Min/Max, hence all above change will be replaced.

You know what I mean?

Now, I'm totally lost.
Did you ever mention?

For example: take the first exercise "Alternating dumbbell bench press" and copy the name to another cell somewhere in column A, then add, let's say "999" to column B of the same row. The value of C2 on the Exercises tab should change from "50" to "999"

Wait for other poster to come in to help you.

jindon, that was just a simple functionality test of the code. You do it to find out whether the code does what it is supposed to do or not. Without testing it, you will never know. In any case, I thank you very much for trying to help. I really do appreciate your time and effort.

Hello, kvsrinivasamurthy! Thank you for your help. In your version, I found the same issues as before with the only difference that the lowest weight values were swapped with the highest weight on the sheet Exercises. I do not see any other difference.

Even worse, it doesn't update via Sheet Activate event.

I think better close this and open a new thread disclosing everything that you are trying to do in the first place.

jindon, everything has been described in my original post. There is nothing new that I added afterward.

jindon, thank you very much for continuing help. The latest file works as you described, thank you for that. However, there are a couple of other things that still do not work as I want them to be. Let me address just one for now, and then will deal with the next one once this first one is working.

This first issue is that each exercise on tab "Week 10" can repeat on other day-sections and the high/low on tab "Exercises" should take them into account. For example, I can have "Alternating dumbbell bench press" exercise on Day 1 and on Day 3, or any other day for that matter. I guess I should have explained this point in my original post as I did not think that I needed to explain it.

try the attached.

jindon, that is great! With that code, my second problem is now also fixed, which I was going to ask next, to make it work on added new tabs. However, there is one little issue with the code: if you add one of already existing exercises to "Week 10" tab, like "Alternating dumbbell bench press", and leave its weight empty, then refresh the "Exercises" sheet, the low for that exercise on "Exercises" sheet will change to 0. I understand it happens probably because it takes the empty cell as the lowest value, but it should not be doing that. What makes it even more of a problem is that even if you do enter the lowest value to the exercise on "Week 10" sheet, for example the value of "1", the low value on "Exercise" sheet will no longer take that "1" and will remain at 0.

The reason I've removed Worksheet_Activate event is that it refresh the data from all the "Week xx" sheet.

The situation like you have changed one of the Exercise Min/Max, it will be gone when you select back the Exercise sheet from any other sheet.

Is that what you want?

I am sorry, I did not quite get that. Could you, please, rephrase?

With the most resent workbook I've posted. (forget about 0 in Min for now)
1) Double click on col.A to refresh the data.
2) change the min/max in any cell.

Now you got different set of Min/Max in particular row(s).

When you add new sheet, it should be refreshed to get new Min/Max from all the Week xx sheet, so changed Min/Max will be replaced with Actual Min/Max.

Are you saying, that is the current process to get the values on "Exercises" sheet updated? What I found is that once I get one exercise on the "Exercise" sheet to display "0", not even adding a new "Week xx" sheet will update the "0" for that exercise on "Exercises" sheet, no matter what value for that exercise has been entered on the new sheet (I hope I don't confuse you with that statement).

That's awesome! Thank you very much, sandy666!

Originally Posted by chrisneu

That's awesome! Thank you very much, sandy666!

You are welcome
to see whole power of Power Query you need PQ add-in in your excel

have a nice day

Oh, yeah! That's what I meant when I said this is great, it works. Yes, that is good. Now, if you're not yet tired with me , I would ask you for help to fix that low-weight "0" issue. And after that, I think, there is a last thing that is still missing: on the current "Week 10" sheet or any other "Week xx" sheet that will be added, when you enter an exercise to column A on that sheet, the high-weight value should auto-populate the column B for that exercise, if it already has the values in "Exercises". This is so that it will give me the starting point with the scheduled exercise so that I know how much weight I had lifted in the past. Once I finish doing that exercise, I should be able to manually overwrite that auto-populated value with the actual weight that I just used, in case it differs.

Is PQ solution satisfied all of our requirements?

If so, no need to post my code.

Oh, no, jindon. That PQ solution was just "for fun" and it does not replace what you have been helping me with. I hope that still can help me with the remaining parts.

This will update all Min/Max whenever Exercise sheet is selected from other sheet.
That means all the changes you manually made to the Min/Max in Exercise sheet will be replaced with actual Min/Max from all the Week xx sheet(s).
Replace current code with

Private Sub Worksheet_Activate()
    Update Range("a2", Range("a" & Rows.Count).End(xlUp))
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Target.Range("b1:c1").ClearContents
        Update Target
    End If
    If Not Intersect(Target, Columns("b:c")) Is Nothing Then
        If IsNumeric(Target) Then FixData Target
    End If
End Sub

Sub Update(rng As Range)
    Dim r As Range, x(2), i As Long, s As String, ws As Worksheet
    Application.EnableEvents = False
    rng.Offset(, 1).Resize(, 2).ClearContents
    For Each ws In Worksheets
        If LCase$(ws.Name) Like "week *" Then
            x(0) = ws.Range("a" & Rows.Count).End(xlUp).Row
            For Each r In rng
                x(1) = Filter(ws.Evaluate("transpose(if(a2:a" & x(0) & "=""" & r & """,row(2:" & x(0) & ")))"), False, 0)
                If UBound(x(1)) > -1 Then
                    For i = 0 To UBound(x(1))
                        s = "b" & x(1)(i) & ":k" & x(1)(i)
                        x(2) = ws.Evaluate("sumproduct((" & s & "<>"""")*(isnumber(" & s & "+0))*(mod(column(" & s & "),2)=0))")
                        If x(2) > 0 Then
                            x(2) = ws.Evaluate("min(if((" & s & "<>"""")*(isnumber(" & s & "+0))*(mod(column(" & s & "),2)=0)*(isnumber(" & s & "+0))," & s & "+0))")
                            If r(, 2) = "" Then r(, 2) = x(2)
                            If r(, 2) > x(2) Then r(, 2) = x(2)
                            x(2) = ws.Evaluate("max(if((" & s & "<>"""")*(isnumber(" & s & "+0))*(mod(column(" & s & "),2)=0)*(isnumber(" & s & "+0))," & s & "+0))")
                            If r(, 3) = "" Then r(, 3) = x(2)
                            If r(, 3) < x(2) Then r(, 3) = x(2)
                        End If
                    Next
                End If
            Next
        End If
    Next
    Application.EnableEvents = True
End Sub

Sub FixData(r As Range)
    Dim x, i As Long, myMin As Double, myMax As Double, temp As Double
    Application.EnableEvents = False
    x = Filter(Evaluate("transpose(if(a2:a50000=a" & r.Row & ",row(2:50000)))"), False, 0)
    If r.Column = 2 Then
        myMin = r
        myMax = Evaluate("max(if((a2:a50000=a" & r.Row & ")*(c2:c50000<>""""),c2:c50000))")
        If myMin > myMax Then myMax = myMin
    Else
        myMax = r
        myMin = Evaluate("min(if((a2:a50000=a" & r.Row & ")*(b2:b50000<>""""),b2:b50000))")
        If myMin > myMax Then myMin = myMax
    End If
    For i = 0 To UBound(x)
        Cells(x(i), 2).Resize(, 2) = Array(myMin, myMax)
    Next
    Application.EnableEvents = True
End Sub

jindon, that fixes the issue with updating the Exercises sheet. That's awesome! Thanks!

You think, you can do the final function with auto-populating the column B on Week xx sheet?

Can you explain how to update col.B of Week xx sheet?

You may have multiple Week xx sheet and col.B of what row? and what value?

Hello, jindon! I need to tell you that in a couple of hours, I will no longer be able to respond for the next 3 days or so as I am traveling.

So, the trigger for the auto-entry to column B should be when you enter a new exercise into column A on any row of any Week xx sheet. As you can see, the exercise entry areas in column A are split by days: it's A6:A15 for day 1, A20:A29 for day 2, and so on for 7 days. Each sheet is one week or 7 days. I add a sheet for next week by right-clicking one of week-sheet's tab and copy. Then, I clear all the existing entries from all days and begin to enter new exercises for each day. That's exactly the time when I want to see what was the maximum weight that I ever lifted for each exercise that I am entering. So, once I am done populating column A with all the exercises for each day of that week, I start my workouts and enter actual lifted weights for each exercise as I go.

This time you need to prepare 2 workbooks without code.

1 with before and 2 with after, clearly showing how you exactly want it.
You have Min/Max for each Exercise, and number of Set within one row.
So I need to see where to enter what value when you add exercise in each Week xx sheet.

jindon, here are the two files. There is one new thing that I added and I wanted to ask you about: on the Exercises sheet, I have added one more column for "Muscle Group". Would you mind to make changes to your existing code so that takes that column into account, meaning the location of Lowest Weight and Highest Weight columns are no longer B and C, but C and D.

I add a sheet for next week by right-clicking one of week-sheet's tab and copy. Then, I clear all the existing entries from all days and begin to enter new exercises for each day. That's exactly the time when I want to see what was the maximum weight that I ever lifted for each exercise that I am entering. So, once I am done populating column A with all the exercises for each day of that week, I start my workouts and enter actual lifted weights for each exercise as I go.

How do you want to know max weight from exercise sheet?
Where to enter the value or just a message box?

the max value goes into column B only. So, for example: entering an exercise into A6 automatically populates B6 with the maximum weight value for that exercise, which should be the same value as found in the D column of the Exercises sheet.

See if this works.

I'll be off line soon, so this is my last post of the day.

jindon, as I am prepairing for my trip, I have very little time right now to check for any potential issues, but at first glance, this workbook works exactly as I wanted! I also like your included button to generate a new Week sheet. Everything looks great! Thank you so much, jindon, for your time and a huge help!