CONCATENATE text to create a formula to be evaluated

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!
0
Utf
1/2/2010 2:09:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1616 Views

Similar Articles

[PageSpeed] 19

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" wrote:

> 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!
0
Utf
1/2/2010 2:21:01 AM
Thanks JLatham!  Happy New Year!

"JLatham" wrote:

> Brian,
> Try rewriting it as  =MAX( INDIRECT(O1 & "!A3:A65536"))

0
Utf
1/2/2010 2:25:01 AM
Reply:

Similar Artilces: