Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Referencing Sheets By Name in Excel

Posted on 2015-01-18
6
Medium Priority
?
135 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
  • 3
  • 2
6 Comments
 
LVL 50

Assisted Solution

by:Martin Liss
Martin Liss earned 800 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 50

Assisted Solution

by:Martin Liss
Martin Liss earned 800 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 18

Accepted Solution

by:
Simon earned 1200 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 50

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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 …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

581 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