Try this with a valid worksheet name in A1

=INDIRECT("'" & A1 & "'!AA26")

Mike

"Dave" wrote:

I am trying to create an excel file with 100 worksheets in it. These 100
worksheets will be having the prices of individual stocks in different years.
After the whole thing I have made one main worksheet that has the final
output from the 100 worksheets but this time only for fixed years, i.e 2001.
Here is how I am doing :

A B C
Name Year Price
MSFT 2001 30 (=msft!$AA$26)
APPLE 2001 35 (=Apple!$AA$26)

This works fine cause I have named the worksheets by the name of the stock.

But when I input (=A3!$AA$26) instead of (=msft!$AA$26) , it gives me an
error. Since I have 100 stocks it is very time consuming to keep on replacing
the name i.e MSFT and Apple and so on.

If I can refer it directly with the column it would be much more easier for
me.
P.S The name in the 1st column is the same as the name of the worksheets.

any help.. would be much appreciated...