Forum Discussion

Steven560's avatar
Steven560
Copper Contributor
Jul 24, 2022
Solved

Need help with an "Overview" Sheet

I have a file that I want to use as a template for future projects. Sheet 1 is titled "Overview" and Sheet 2 is titled "Project ABC". I have two questions that I need help answering to complete this template.

 

Question 1: I was able to find online the below formula to have a cell return the name of the sheet.

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

 

Is there a formula to return the name of a different sheet?

 

I want to use this on the overview sheet to simply pull the title of sheet 2. When I change the title of Sheet 2, it changed the title on the overview.

 

Question 2: I'm not sure if Excel has this capability but worth an ask. The overview page currently pulls data from sheet 2 and displays it in cells A4:C18. Is there a way in which these cells are copied to create another overview section below it when a new copy of sheet 2 is created? Not a new overview sheet, just a copy of the cell's formula in for example A19:C33 - but referencing the new sheet 2 copy in the formulas.

 

To help explain this: I may want to use this file for a project that is separated into multiple phases. I would call sheet 2 "Project ABC Phase 1", make a copy of it called "Project ABC Phase 2", then 3...4...and so on. Is there a way in Excel for each new sheet to automatically create a new section and populate data on the overview page? The data will be in the same spot obviously since sheet 2 will be copied.

 

______

 

Thank you all very much for your help.

  • Steven560 

     

    Re: Question 1.

    Yes, use this:

     

    =RIGHT(CELL("filename",'Project ABC'!A1),LEN(CELL("filename",'Project ABC'!A1))-FIND("]",CELL("filename",'Project ABC'!A1)))

     

    If you change the name of Project ABC, Excel will automatically change the reference in the formula.

Resources