Forum Discussion

Connor Copeland's avatar
Connor Copeland
Copper Contributor
Apr 29, 2018
Solved

Trying to figure out to run one calculation across multiple sheets

Hello, I have an excel file set up where the first page is a series of calculations and tables related to information on the proceeding pages. Basically I am trying to run a formula that looks like...
Formulas & Functions
Need Help
  • Connor Copeland's avatar
    Connor Copeland
    May 01, 2018

    Got It!
    The formula works like
    =(SUM(INDIRECT("'"&A2&"'!"&"$G$3:$G$10000"))/B2)
    This will save me countless hours and errors.

Logaraj Sekar's avatar
Logaraj Sekar
Steel Contributor
May 01, 2018

Hi,

 

Remove '1' in the formula. Coz, =SUM('1'!$D$2:$D$100000)/$B2 will work only for sheet named as 1.

 

Put formula as =SUM($D$2:$D$100000)/$B2 which will work on any sheet.

Connor Copeland's avatar
Connor Copeland
Copper Contributor
May 01, 2018

Got It!
The formula works like
=(SUM(INDIRECT("'"&A2&"'!"&"$G$3:$G$10000"))/B2)
This will save me countless hours and errors.

  • Monica Bossert's avatar
    Monica Bossert
    Brass Contributor
    Feb 14, 2020

    Connor Copeland 

    Hello, 

     

    I have a very similar situation. I got it to reference the tabs properly, but in my situation I need to not only carry the formulas down, but I also need to carry it sideways on my summary tab. 

    In your formula, where you have B2 I have C20. In the column beside, I need it to have the same formula, only D20. When I drag it, C20 is staying. 

    Any ideas? 

     

    Thanks