Is it possible to utilize the Min function with a dynamic range

[DonEB]

I have a range of data which I'm trying to determine what the minimum is. I understand the basic formula =MIN(D53:AG82). My problem is that my range needs to be dynamic to account for the frequently changing parameters.

The range of D53:AG82 is the largest range I need to test and is easily tested using =MIN(D53:AG82). This is required when I'm searching for the min value involving 30 players.

My problem comes when I need to find the min value for a range that is less than the maximum.
Example:

1. The starting point will always be D53.
2. I can predetermine the number of players involved. In this example 8 players (this value is established in a separate worksheet called ALLWEEKS:G79)
3. I can sort all data to place "active" players information up front (i.e., D53:K60)

I don't know how to dynamically change the AG82 down to K60 using the value contained in ALLWEEKS:G79. Is this possible?

Thank you for any guidance you can provide.
Don

 

[FormR]

Hi, you could try this:

=MIN(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79))

 

[DonEB]

Sorry for my slow response... I ran into a few other issues I had to resolve before I could put your proposed solution to the test.

And... thank you... this seems to work perfectly.

 

[DonEB]

Hi, you could try this:

=MIN(D53:INDEX(D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79))

If I could possibly ask you to take this one step further... I'm trying to incorporate this into some vba. Based on some reading it appears that I would need to use something like WorksheetFunction.Min to accomplish this. However, this has a tendency to confuse me.

To fill in some blanks that may be needed,

1. the chart containing the players, etc is in a worksheet called CommonData
2. As you already know, the place where the number of players are identified is on Allweeks:G79

Am I on the right track?

 

[FormR]

Here's one way you could use the formula in VBA, for example:

MsgBox Evaluate("MIN(CommonData!D53:INDEX(CommonData!D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79))")

 

[DonEB]

Awesome... I believe this works well.

I have an area within my vba where I would like to place this code but I'm not certain who to make the changes necessary to make it work.

wsName2 = "CommonData"

With Sheets(wsName2)
'Note: I understand how to assign a single value to a variable within but I'm not sure how to assign a range of values within this logic.
' Example: A = .Range("z5").Value

'However, I'm not sure how to take the formula you provided (copied below) and convert it to work with this area.
' Min = Evaluate("MIN(CommonData!D53:INDEX(CommonData!D53:AG82,ALLWEEKS!G79,ALLWEEKS!G79))")

End With

If you could provide this final guidance, it would be greatly appreciated.

 

[FormR]

I'm not entirely sure what you are trying to do, but you can try something like this:

Sub AnExample()

Dim wsName2 As String
Dim NumPlayers As Long
Dim fxResult As Double 'change as appropiate

NumPlayers = Sheets("ALLWEEKS").Range("G79")

wsName2 = "CommonData"

With Sheets(wsName2)
    fxResult = Application.Min(.Range("D53").Resize(NumPlayers, NumPlayers))
End With

MsgBox fxResult

End Sub

p.s. A tip for the future, if you need a VBA solution then it's usually best to mention that from the outset

 

[DonEB]

My apologies and your comment has been duly noted. My initial problem was within the worksheet which is what my original question was based on. I evolved into a VBA solution since I've been examining placing as much code into VBA in order to speed up a very time-consuming process.

That being said, your proposed solution works great. In fact, both of your solutions worked just as I would have hoped. Thank you for your help.