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 |

1/2/2010 2:09:01 AM

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 |

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 |

1/2/2010 2:25:01 AM