Solved

Referencing Sheets By Name in Excel

Posted on 2015-01-18
6
82 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 45

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 45

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 18

Accepted Solution

by:
SimonAdept 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 45

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

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 briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now