Solved

Referencing Sheets By Name in Excel

Posted on 2015-01-18
6
93 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 46

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 46

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 46

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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