MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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?


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!