CONCATENATE text to create a formula to be evaluated

[Brian]

Hi,

I am wanting to concatenate a set of text to create a formula. I have done
so below.

=CONCATENATE("=MAX('",O1,"'!A3:A65536)")

The result is: =MAX('2009'!A3:A65536)
....but it treats this as a text string when I try to use INDIRECT

Cell O1 contains the year minus 1. In this case O1 = 2009. All of my
worksheets are named as a year e.g. 2007....2008....2009...2010. I am
entering this on sheet 2010.

Now the real question: How do I make another cell evaluate this string as an
actual formula and spit out the highest number for A3:A65536 from sheet 2009.

Thanks and Happy New Year!

 

[JLatham]

Brian,
Try rewriting it as
=MAX( INDIRECT(O1 & "!A3:A65536"))
or even more simply (as long as A1 & A2 on the sheets don't have numbers in
them
=MAX( INDIRECT(O1 & "!A:A"))

 

[Brian]

Thanks JLatham! Happy New Year!