SOLVED

Need help with an "Overview" Sheet

Occasional Contributor

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.

10 Replies

@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.

This works perfectly for Question 1. Thank you very much.

Any idea on Question 2?

@Steven560 

See the attached sample workbook. You'll have to allow macros.

@Hans Vogelaar Seems to work in your sample workbook. Can't get it to work on my own. I've attached my workbook for you to play with. Should help you better understand my desire as well. Can you see if you can get this to function?

 

Thanks.

Also I'm not sure if the placement I have for the "Add Office/Phase" button is okay. Is there a way we can have it move down as new overviews are added? If not, I will find a new location for it to stay.

@Steven560 

See the attached version. I made the references in the formulas in column C absolute, so that the references will remain the same when the range is copied.

The workbook is .xlsm so you will have to allow macros.

@Hans Vogelaar That worked great. I added an additional row to help separate the overviews a bit and I broke the code. Tried to correct the code to include the additional rows and I broke it...

 

I'm not sure how this code works so I have to rely on your expertise to fix this. Sorry to ask you a second time, but please take a look and make the correction.

 

Also, just to clarify, is this code searching for and making a copy of "TEMPLATE"? If I change the title of TEMPLATE to something else, will it still work?

@Steven560 

Here is a new version. It should still work if you change the name of the TEMPLATE sheet, as long as it is immediately to the right of the Estimate Overview sheet.

See next reply

best response confirmed by Steven560 (Occasional Contributor)
Solution

@Steven560 

Oops - the code still depended on the name "TEMPLATE"at one point. I have corrected it.

See attached version.

You're awesome. I greatly appreciate your help with perfecting this document.