Solved

Referencing Sheets By Name in Excel

Posted on 2015-01-18
6
103 Views
Last Modified: 2015-01-18
I have a worksheet that has a sheet for each event that I time.  I want to keep a summary page, referencing the sheets themselves by name and values on those sheets on the summary page.  So really I have two questions:

1) How can I reference the sheet names in a cell on the summary page so that I can just drag down and fill based on the sheet number.  This would be what I want in column b.

2) how can I incorporate cell values from sheets by sheet number rather than by sheet name in my formulas.

Thanks!
0
Comment
Question by:Bob Schneider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 200 total points
ID: 40556268
1) =Sheet2!A1

I don't think it's possible to do the second.
0
 

Author Comment

by:Bob Schneider
ID: 40556291
Thank you but when I entered that it tries to open another workbook.  A file search dialog appears.
0
 
LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 200 total points
ID: 40556296
Change "Sheet2" to the name of a sheet in your workbook. In other words if you have a sheet called "Summary" then do


=Summary!A1

It looks for another workbook when the sheet name doesn't exist in the current workbook.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 18

Accepted Solution

by:
Simon earned 300 total points
ID: 40556298
If you store the index number of the sheet in column A of your summary and the sheets are named "sheet1", "sheet2" etc, you can use the INDIRECT function. If you drag this example down in column B it will refer to the cell A1 of each of the sheets:
=INDIRECT("SHEET"&A1&"!$A$1")

If you wanted to refer to sheets by their index number in the activeworkbook.worksheets collection, I think you'd need a vba routine to populate a lookup list (and re-run the routine on workbook_open or when you have re-ordered the sheets).
e.g.
1   Timings2014-11-14
2   Timings2014-11-15
3   Timings2014-11-16

You could then use that list to vlookup the sheet name and use it in the indirect function.

However, this would be error-prone (if you forgot to re-run the vba routine after switching sheet order). It might help to tell us a bit more about WHY you want to refer to values on sheets according to their order in the workbook. We might be able to suggest another approach to solving your problem.

e.g. If you want stats from '3 most recent' events etc, you might be better off (manually or using VBA) to rebuild a set of formulae that refer to the 3 sheets to the left (or right) of the summary sheet.
Or, you might modify the design of the event worksheets to make them suitable for use as multiple consolidation ranges.
0
 

Author Comment

by:Bob Schneider
ID: 40556311
Thank you both very much.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40556328
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question