?
Solved

Using Cell Reference as Worksheet Name

Posted on 2013-12-02
6
Medium Priority
?
370 Views
Last Modified: 2013-12-02
I have a spreadsheet that I have created to track totals for various worksheets in a workbook. Each time I add a new worksheet to the workbook I have to set up a series of formulas on a summary page to pull information from the newly created worksheet. To do that I have to manually type my new worksheet name into the formula each time. What I want to know is there a way to use a cell reference to populate the worksheet name rather than typing it in each time.

Here is one of my formulas which is in cell B3 on my summary worksheet.

=INDEX('Sheet'!$B$11:$B$100001, COUNTA('Sheet1'!$B$11:$B$100001), 1)

However in A3 of that same sheet I have the worksheet name, Sheet1, entered. What I would like to do is use a cell reference that would translate through the formula as "Sheet1" but everything I have tried has returned a #Ref! error. For example:

=INDEX('"3"'!$B$11:$B$100001, COUNTA('"A3"'!$B$11:$B$100001), 1)

does not work. I have tried several different variations of this same thing but everything I try returns the exact same error message.

Is this doable or is it impossible?
0
Comment
Question by:gacto
[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
6 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 2000 total points
ID: 39690943
Try:

=INDEX(INDIRECT("'"&A3&"'!$B$11:$B$100001"), COUNTA(INDIRECT("'"&A3&"'!$B$11:$B$100001")), 1)
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39690987
This formula will give the sheet name in a saved file.

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39691102
Slight addition to ssaquibh's suggestion.

On each sheet use:

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

That will then give the sheet name for that sheet whereas previously the formula would give the sheet name for the active sheet.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:Rob Henson
ID: 39691120
I have also something similar previously with the MID function.

On your summary sheet, are the sheet names hard coded or formula driven. If hard coded and the sheet name of the source sheet changes, the INDEX formula above won't be able to find the sheet and will give an error.

You can use this in cell A3 where your current sheet name is:

=REPLACE(CELL("filename",Sheet1!$A$1),1,FIND("]",CELL("filename",Sheet1!$A$1)),"")

The reference to sheet1 will then change with a change in sheet name and the calculated result would be the new sheet name, a self fulfilling prophecy as Wizards would say!

Thanks
Rob H
0
 

Author Closing Comment

by:gacto
ID: 39691165
Thanks for the quick response nb_vc.
0
 

Author Comment

by:gacto
ID: 39691173
robhenson,

my sheet names are hard coded but you bring up an interesting point. I will post another question to determine how to implement your suggestion. I do not know how to write the formula to populate my cell references with the sheet names. Ultimately this workbook will have new worksheets added on a regular basis so your suggestion will be extremely helpful.
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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

800 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