PDA

View Full Version : Solved: Sheet names in formulae



mdmackillop
08-21-2006, 04:55 AM
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.

xld
08-21-2006, 05:24 AM
=MAX("'"&INDIRECT(B2)&"'!K:K)

mdmackillop
08-21-2006, 06:00 AM
I knew it had to be in there somewhere,
Thanks Bob

gnod
08-21-2006, 06:11 AM
does it work? but when i copy the formula it contains an error.. it says "The formula you type contains an error." :confused:

xld
08-21-2006, 06:17 AM
You are absolutely right, it should be

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

gnod
08-21-2006, 06:23 AM
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
08-21-2006, 07:55 AM
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
08-21-2006, 09:37 AM
thanks zack.. i'll do my best to participate in this forum.. :reading::thinking::type

mdmackillop
08-21-2006, 10:06 AM
...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
08-21-2006, 11:43 AM
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: