Avatar of gacto
gacto asked on

Using Cell Reference as Worksheet Name

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?
Microsoft Excel

Avatar of undefined
Last Comment
gacto

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
NBVC

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Saqib Husain

This formula will give the sheet name in a saved file.

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")
Rob Henson

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.
Rob Henson

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
gacto

Thanks for the quick response nb_vc.
ASKER
gacto

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.