Forum Discussion

Sameer_Kuppanath_Sultan's avatar
Sameer_Kuppanath_Sultan
Brass Contributor
Jan 07, 2020

How to link to every sheet whenever we create a new sheet into a common place

Hi Every Ony

 

Happy New Year

 

How do I give a formula to the same cell whenever I create a new sheet?

For Eg:When I will create sheet4, and I want to have the same data in c6 by linking c5 in sheet4

 

Please see the attached sheet!

excel

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 07, 2020

    Sameer_Kuppanath_Sultan 

    First, you shall list in summary sheet actual sheet names; or use some logic which generates sheet names.

     

    Assuming sheet names are Sheet2, Sheet3, etc and in Summary we use only the number at the end of the sheet name, formula in C6 could be

    =IFERROR(INDIRECT("Sheet" & A6 & "!C5"),"")

    and drag it up/down.

  • Abiola1's avatar
    Abiola1
    MVP
    Jan 07, 2020

    Hello Sameer_Kuppanath_Sultan 

    • In the Summary sheet, type in =SUM(
    • Click on Sheet2 and select C5
    • Then, hold the SHIFT key on the keyboard and click on Sheet3
    • Click Enter

    This is called Group Sheet technique

    Do let me know whether this answers your question




Resources