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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 11:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com