MAX and INDIRECT forumla

[RedtoGreen]

I am trying to use the formula MAX(A1:A100) where both the cell references are in fact results of other formulas ie A1 is actually this...

(code in cell AH29)

Please Login or Register  to view this content.

and A100 is actually this...

(code in cell AG29)

Please Login or Register  to view this content.

At the suggestion of another user, I've got the following

Please Login or Register  to view this content.

which gives me the max between cell J16 and the cell referenced in AG29, but what I would like to do it have both the two cells at either end of the range (J16 in this case) the result of an indirect or similar.

Any thoughts about how to make my MAX formula? If you need more to re-word this, let me know!

To summarise, I want a MAX formula, where both cell references are results of other formulas. How do I achieve this?

[JBeaucaire]

You've been originally given an INDIRECT() formula that shows how to hardcode part of the string and then retrieve the other half from a cell. Did you try making both halves of your formula use the syntax of the second half?

=MAX(INDIRECT(AH29 & ":" & AG29))

[RedtoGreen]

I've figured out how to do it.. will post again in a little bit to explain how.. Just wanted to make sure none of you hard-working intelligent people are trying to solve it on my behalf!

[JBeaucaire]

Did your solution differ from the one I suggested in post #2? Always remember to post solutions in your thread(s) when you self-solve.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.