Refering cell to the worksheet name

D

Dave

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

Ashish Mathur

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
 
D

Dave

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
 
M

Mike H

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
 
S

ShaneDevenshire

Hi,

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

=INDIRECT(A3&"!AA26")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
VBA Coding Help for Beginner 0
Maintain lowest value in an "external connection" cell 0
How to update data entry to a linked worksheet and then return thenew value back to a master workshe 0
Worksheet Names using Macros 5
Trying to average the contents of a cell across multiple worksheet 12
Define a Range based on Cell Values 2
Sum of different value in different worksheet & cell number 2

Top