• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Update Cell References with New Sheet Names

I have a summary worksheet in a workbook that will be updating with data from many different worksheets. Each time I add a worksheet I will need to add a cell reference on the summary sheet that will update with the worksheet name, even if I make changes to the worksheet name in the future. Is there a formula that I can use for this?
0
gacto
Asked:
gacto
  • 2
1 Solution
 
nutschCommented:
You can use the INDIRECT function to reference a reference on a sheet with the sheet name. I don't know of any formula that will retrieve the name of the last inserted sheet.

Can you provide a more detailed example if the help on INDIRECT doesn't lead you to the right solution?

Thomas
0
 
Rob HensonFinance AnalystCommented:
As suggested previously:

=MID(CELL("filename",Sheet1!$A$1),FIND("]",CELL("filename",Sheet1!$A$1),1)+1,50)

Put on your summary sheet. As you add new sheets you will have to change the sheet reference for the new entry but that can be done with a simple Search and Replace. Highlight the cell that requires changing and a blank cell below it ( I assume immediately below will be blank) and press Ctrl + H.

Find:  Sheet1
Replace with: Sheet2

Click Replace All

Change sheet names above accordingly. Selecting the two cells will only change the entries in those two cells whereas not selecting will change all entries on the sheet.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
@Thomas Nutsch

To keep you in the loop, this is a follow on from previous question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28308329.html#a39691173
0
 
gactoAuthor Commented:
Rob H,

works like a charm. thanks for the suggestion.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now