Trying to figure out to run one calculation across multiple sheets

[Connor Copeland]

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 =(SUM('1'!D2:D100000)/B2).
This captures the sum of an entire column in a particular page and divides it by its appropriate reference number.
Unfortunately, when I try to scale this to my entire sheet all of the wrong things, and none of the right things, get pulled down.
-When I finish the table I want '1' (the sheet I am referencing) to change, so the next row is 2, then 3, and so on. As of now this stays static at 1 and I have to manually change each column.
-I want "D2:D100000" To stay constant, I am trying to capture an entire column from the referenced sheet. As of now when I try to run this the next column goes "D3:D100001" and so on.
-Finally, I want "B" to stay, but "2" in "B2" to change so it goes B2, B3, B4 ect.

So when I type this in excel, and drag it to finish the table, the next formula should look like
=(SUM('2'!D2:D10000)/B3)

As of now the next formula would look like
=(SUM('1'!D3:D10001)/B3)
Anyone have any ideas?

[#2]

Hi

Try

=SUM('1'!$D$2:$D$100000)/$B2

[#3]

Hi,

Try this =SUM('1'!$D$2:$D$100000)/$B2

[#4]

Hello,
Thank you for the quick reply! That takes care of the row shifts in D2-D10000, but the sheet number still stays on 1 unfortunately.

[#5]

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.

[#6]

Okay, how do i get it to reference multiple sheets then? The goal is for each row to be a table of an individual sheet, and each column to be the same calculation done on all of those sheets

[#7]

Update,
I think I am onto something now. My first column in the "working" page is a list of all the sheet names as reference. I think I could use an indirect formula to grab that, but I'm not sure how to properly set it up.
Here is what I have so far:
=INDIRECT(A2 & !(SUM($G$2:$G$10000)/B2))
That should return 1!(SUM($G$2:$G$10000)/B2))
A possible variation would be like:
=("'"&A2&"'"&!(SUM($G$2:$G$10000))/B2)

But I'm not able to get either of these to be recognized as a formula for some reason.

[#8]

I realized I had the wrong order of things in those equations so now I have:
=(SUM(&"'"&A2&"'"&!$G$3:$G$10000))/B2
or
=Indirect((SUM(&"'"&A2&"'"&!$G$3:$G$10000))/B2, True/False? I'm not sure which one)

Both of those are still telling me invalid formula though

[#9 · Accepted solution]

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

[#10]

@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