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 ...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Jul 24, 2022

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.

Steven560's avatar
Steven560
Copper Contributor
Jul 24, 2022
This works perfectly for Question 1. Thank you very much.

Any idea on Question 2?
    • Steven560's avatar
      Steven560
      Copper Contributor
      Jul 24, 2022

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

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 24, 2022

        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.

Resources