I need to determine the maximum value in a dynamic range. The values
(pressures) are in Column S. The starting row# is in cell Q5 and the ending
row number is in cell R5. I do not know how to specify the column and
associated row # when the row # is contained in a cell. I tried the
following and similar variations:

=Max("S" &"Q5" : "S" &"R5")

Everything that I have tried so far results in an error message.

Is there an online tutorial for learning how to specify column/row
references when the column and/or row # is contained in a cell? Thanks for
any help and pointers on this request. I am trying to learn Excel by trial
and error and it is taking awhile. From tips and responses on this website,
I have figured out how to use dynamic ranges (Offset command) for charting
series, queries for an SQL database, and macros. I've made a lot of headway
in recent weeks but I am still stumbling, often, on what may be the simpler
To Do things. Thanks for listening.

=Max(Indirect("S" & Q5 & ":S" & R5))
(untested, but should be close)

The "indirect" tells Excel to read several pieces of information all
together as a range reference.

HTH,
Keith

"Cinco" wrote:

> I need to determine the maximum value in a dynamic range. The values
> (pressures) are in Column S. The starting row# is in cell Q5 and the ending
> row number is in cell R5. I do not know how to specify the column and
> associated row # when the row # is contained in a cell. I tried the
> following and similar variations:
>
> =Max("S" &"Q5" : "S" &"R5")
>
> Everything that I have tried so far results in an error message.
>
> Is there an online tutorial for learning how to specify column/row
> references when the column and/or row # is contained in a cell? Thanks for
> any help and pointers on this request. I am trying to learn Excel by trial
> and error and it is taking awhile. From tips and responses on this website,
> I have figured out how to use dynamic ranges (Offset command) for charting
> series, queries for an SQL database, and macros. I've made a lot of headway
> in recent weeks but I am still stumbling, often, on what may be the simpler
> To Do things. Thanks for listening.

VBA
MsgBox WorksheetFunction.Max(Range("S" & Range("Q5") & ":S" & Range("R5")))

Worksheetfunction
=MAX(INDIRECT("S" & Q5 & ":S" & R5))


If this post helps click Yes
---------------
Jacob Skaria


"Cinco" wrote:

> I need to determine the maximum value in a dynamic range. The values
> (pressures) are in Column S. The starting row# is in cell Q5 and the ending
> row number is in cell R5. I do not know how to specify the column and
> associated row # when the row # is contained in a cell. I tried the
> following and similar variations:
>
> =Max("S" &"Q5" : "S" &"R5")
>
> Everything that I have tried so far results in an error message.
>
> Is there an online tutorial for learning how to specify column/row
> references when the column and/or row # is contained in a cell? Thanks for
> any help and pointers on this request. I am trying to learn Excel by trial
> and error and it is taking awhile. From tips and responses on this website,
> I have figured out how to use dynamic ranges (Offset command) for charting
> series, queries for an SQL database, and macros. I've made a lot of headway
> in recent weeks but I am still stumbling, often, on what may be the simpler
> To Do things. Thanks for listening.

Keith (Ker_01) and Jacob,

I used your Indirect example and it worked beautifully. I have now moved on
to another challenge. I will try to figure my new one out by myself, but
will return to this site with a question if I can't. Thanks for all the help
you folks provide. Your help has been the difference between my succeeding
and not on my latest project.
Jim

"Jacob Skaria" wrote:

> VBA
> MsgBox WorksheetFunction.Max(Range("S" & Range("Q5") & ":S" & Range("R5")))
>
> Worksheetfunction
> =MAX(INDIRECT("S" & Q5 & ":S" & R5))
>
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Cinco" wrote:
>
> > I need to determine the maximum value in a dynamic range. The values
> > (pressures) are in Column S. The starting row# is in cell Q5 and the ending
> > row number is in cell R5. I do not know how to specify the column and
> > associated row # when the row # is contained in a cell. I tried the
> > following and similar variations:
> >
> > =Max("S" &"Q5" : "S" &"R5")
> >
> > Everything that I have tried so far results in an error message.
> >
> > Is there an online tutorial for learning how to specify column/row
> > references when the column and/or row # is contained in a cell? Thanks for
> > any help and pointers on this request. I am trying to learn Excel by trial
> > and error and it is taking awhile. From tips and responses on this website,
> > I have figured out how to use dynamic ranges (Offset command) for charting
> > series, queries for an SQL database, and macros. I've made a lot of headway
> > in recent weeks but I am still stumbling, often, on what may be the simpler
> > To Do things. Thanks for listening.