Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Max value in a dynamic range
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Max value in a dynamic range
=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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Max value in a dynamic range
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Max value in a dynamic range
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
determining dynamic print area | Excel Programming | |||
dynamic range based on criteria, within a dynamic range, passed to a function | Excel Programming | |||
creating a dynamic range based on criteria, within a dynamic range, and passing it to a function | Excel Programming | |||
Determining whether dynamic array has been used | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |