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...

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...

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...

Thank you for the reply

But when I am putting the Details it says that it has an error. It is not
able to get the result.

A B C
1 Name Year Price
2 MSFT 2001 30
3 APPLE 2001 35


The first line is exactly the same as shown above and the price is in the
worksheet with the name MSFT in the cell AA26

I entered :
=INDIRECT("" & A2 & ""!AA26")

this is giving me an error. looking forward to your reply




"Mike H" wrote:

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...

It gave you an error because you copied the formula incorrectly. It's
difficult to see bur if you zoom you will see it contain " and ' characters

Copy and paste the formula and it will work.

Mike

"Dave" wrote:

Thank you for the reply

But when I am putting the Details it says that it has an error. It is not
able to get the result.

A B C
1 Name Year Price
2 MSFT 2001 30
3 APPLE 2001 35


The first line is exactly the same as shown above and the price is in the
worksheet with the name MSFT in the cell AA26

I entered :
=INDIRECT("" & A2 & ""!AA26")

this is giving me an error. looking forward to your reply




"Mike H" wrote:

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...

Hi Dave

try
=INDIRECT(""&A3&"'!AA26")

--
Regards
Roger Govier

"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...

Hi,

If you are still having problems with this try slightly shorter version:

=INDIRECT(A3&"!AA26")

--
Thanks,
Shane Devenshire


"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...