Forum Discussion
Steven560
Jul 24, 2022Copper Contributor
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.
Oops - the code still depended on the name "TEMPLATE"at one point. I have corrected it.
See attached version.
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.
- Steven560Copper ContributorThis works perfectly for Question 1. Thank you very much.
Any idea on Question 2?