Archive of Mr Excel Message Board
Back to Dates in Excel archive index
Back to archive home
use cell value as worksheet name in formula reference
Posted by Larry on July 20, 2001 12:38 PM
I have a workbook that has many worksheets.
The worksheet names are listed on another worksheet in the same workbook called summary.
I want to use the worksheet name as refenced in a cell and use it in a formula.
ie:
worksheet name is 0003
on the sumary page cell a1 is 0003
I want a column total from 0003:B
This works but is almost unmaintainable.
=SUM('0003'!B:B)
what I want to do is replace the 0003 in the formula with a reference to a1
I tried =SUM(a1!B:B) but it says "File not found"
Any Ideas?
Re: use cell value as worksheet name in formula reference
Posted by Mark W. on July 20, 2001 12:44 PM
=SUM(INDIRECT("'"&A1&"'!B:B")). But... make sure
that A1 contains "0003" and not 3 formatted as 0003!
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.