Reference to a specific sheet in Excel

Is there a way to reference some sheet in Excel, giving its name in a Cell ?
For example, I have sheets named January, March, April etc...
I have a final sheet named, says 2015
I how I can refer cell A1 of March with March!A1
But can I put the string March in a cell of sheet 2015, says A1 and have in A2 the value of cell named by combining A1, !, and A1 or similar ?
LVL 1
LeTayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Missus Miss_SellaneusCommented:
"But can I put the string March in a cell of sheet 2015, says A1 and have in A2 the value of cell named by combining A1, !, and A1 or similar ? "

Could you try to explain that a little clearer with an example perhaps?
0
Roy CoxGroup Finance ManagerCommented:
Not really sure what you mean. Do you mean summarise A1 for each monthly sheet? If so look at this

Summarise data
0
Saurabh Singh TeotiaCommented:
You need to use this formula...

=indirect(a1&"!"&a2)

Open in new window


Assuming A1 has the sheet name and a2 has the cell reference which you want to pick up..

Saurabh...
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

nutschCommented:
Saurabh is correct. If your sheet name happens to have spaces or other non-kosher characters, you'll need to use single quotes around the sheet name in your formula, e.g.

=indirect("'"&a1&"'!"&a2)

Open in new window


that (doublequote singlequote doublequote & a1 & doublequote singlequote !
0
LeTayAuthor Commented:
In fact I need a little bit more
I need to have in the 2015 sheet the SUM of a column of the other sheet for which the name is in a cell of 2015
Is this still possible ?
Or other functions than just SUM ?
0
Saurabh Singh TeotiaCommented:
Yes you can do this by...

=sum(indirect(a1&"!"&a2))

Open in new window


This will give you sum of sheet name which you entered in a1 and range which you entered in a2...

Saurabh...
0
LeTayAuthor Commented:
Trying that ...
0
LeTayAuthor Commented:
Well, my final goal is still a little bit more complex
The referenced sheet contains in fact two columns A and B
A contains a letter like a,b,... and B a number
The result in 2015 sheet should be the sum of B number for which A contains, for example 'c' ...
0
Saurabh Singh TeotiaCommented:
Letay,

Can you post a sample workbook of yours as it will be easy to provide a solution for you post looking at what you looking for..

Saurabh...
0
LeTayAuthor Commented:
Here it is
Cell C1 of sheet 2015 should contain 7 which is the sum of column B of February sheet that has column A = c
0
LeTayAuthor Commented:
0
Saurabh Singh TeotiaCommented:
I'm assuming this is what you are looking for..

Saurabh...
test.xlsx
0
LeTayAuthor Commented:
Hum, i still have a small question : name of sheet referenced contain spaces ...
0
Saurabh Singh TeotiaCommented:
You need to add single quotes like thomas posted..enclosed your workbook with revised example with space..

Saurabh...
test.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.