Hi,

This formula will pick up the value from cell B2 of the sheet named MSFT.
This formula assumed that A1 carries MSFT. You can now copy this down

INDIRECT(A1&"!"&"B2")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dave" wrote in message
...
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...