Forum Discussion
Trying to figure out to run one calculation across multiple sheets
Got It!
The formula works like
=(SUM(INDIRECT("'"&A2&"'!"&"$G$3:$G$10000"))/B2)
This will save me countless hours and errors.
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.
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.
Got It!
The formula works like
=(SUM(INDIRECT("'"&A2&"'!"&"$G$3:$G$10000"))/B2)
This will save me countless hours and errors.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
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 thoughUpdate,
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.- 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
