how do I write a formula where the number portion

Question

In cell G14 I have the formula =MAX(D2:D14)

I plan to copy this formula to all the cells in the column G. But, the formula needs to vary so that it is not always working against a fixed range.

What I want is something like: =MAX(D2:D(H14)) That would pull the number portion of the cell address from the cell to the right.

Answer 1

=MAX(INDIRECT("D2:D" & H14)) will do it, assuming H14 contains the number.

But do note that using INDIRECT makes the workbook volatile.

Answer 2

To work against the volatile behavior of INDIRECT most of the times solutions using INDEX are also possible. So here too.

=MAX(D2:INDEX(D:D,H14))

About volatile behavior: Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly.