Solved: Sheet names in formulae

[mdmackillop]

Mental blank time again?
In cell D2 I have the formula =MAX('Sheet2'!K:K). If I enter "Sheet2" in cell B2, can I refer to B2 so that I can copy down to refer to each sheet?
I can accomplish this with =MAX(INDIRECT(B2)) if I change B2 to Sheet2!K:K but for flexibility, would like to avoid this change.

[Bob Phillips]

=MAX("'"&INDIRECT(B2)&"'!K:K)

[mdmackillop]

I knew it had to be in there somewhere,
Thanks Bob

[gnod]

does it work? but when i copy the formula it contains an error.. it says "The formula you type contains an error." :confused:

[Bob Phillips]

You are absolutely right, it should be

=MAX(INDIRECT("'"&B2&"'!K:K"))

[gnod]

you know i'm trying to solve your formula but i'm so stupid :banghead: i didn't even think that, maybe time will come.. : pray2:

[Zack Barresse]

And of course, the UDF ...

http://vbaexpress.com/kb/getarticle.php?kb_id=213

I'd still take the INDIRECT, but fwiw.

And gnod, you're definitely not stupid. Bob is very good with this stuff and he just screwed up something very, very easy. We ALL do it. :yes

[gnod]

thanks zack.. i'll do my best to participate in this forum.. :reading::thinking::type

[mdmackillop]

...and he just screwed up something very, very easy. We ALL do it. :yes
and I spent 10 minutes trying to get these **** apostrophes in the right place. Thanks for pointing it out.

Zack,
It's a real bad day here. I knew that there must be a simple solution, so the thought of creating a UDF never crossed my mind. :banghead: Here it is for anyone who wants it.

Function DoMax(Data As String, Col As Long)
DoMax = Application.WorksheetFunction.Max(Worksheets(Data).Columns(Col))
End Function

[mdmackillop]

thanks zack.. i'll do my best to participate in this forum.. :reading::thinking::type

Hi Gnod,
You missed a couple.

:reading::thinking::type :banghead: :igiveup: