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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
=MAX("'"&INDIRECT(B2)&"'!K:K)
Last edited by mdmackillop; 08-21-2006 at 10:09 AM. Reason: See revised formula below
I knew it had to be in there somewhere,
Thanks Bob
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
does it work? but when i copy the formula it contains an error.. it says "The formula you type contains an error."
You are absolutely right, it should be
=MAX(INDIRECT("'"&B2&"'!K:K"))
you know i'm trying to solve your formula but i'm so stupidi didn't even think that, maybe time will come..
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
thanks zack.. i'll do my best to participate in this forum..
and I spent 10 minutes trying to get these **** apostrophes in the right place. Thanks for pointing it out.Originally Posted by firefytr
...and he just screwed up something very, very easy. We ALL do it.
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.
[vba]Function DoMax(Data As String, Col As Long)
DoMax = Application.WorksheetFunction.Max(Worksheets(Data).Columns(Col))
End Function
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hi Gnod,thanks zack.. i'll do my best to participate in this forum..
You missed a couple.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
