• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

Referencing Sheets By Name in Excel

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
Bob Schneider
Asked:
Bob Schneider
  • 3
  • 2
3 Solutions
 
Martin LissOlder than dirtCommented:
1) =Sheet2!A1

I don't think it's possible to do the second.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
Thank you but when I entered that it tries to open another workbook.  A file search dialog appears.
0
 
Martin LissOlder than dirtCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SimonCommented:
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
 
Bob SchneiderCo-OwnerAuthor Commented:
Thank you both very much.
0
 
Martin LissOlder than dirtCommented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now