Hi
I would like to create a sum formula referencing cell ranges that are constructed from multiple cells (one cell for the sheet range, one for the column, on for the row). In the example pictured below I am attempting to sum a specific cell from all of the sheets between the "START" sheet and the "END" sheet. I would like to use cell references to define the ranges as I will have a large number of formulas built up in this way and it would be easier to control the structure. The sheet range will be static, but the columns and rows will change. I have attempted to use the following formula which results in a #REF! error:
Cell $W$101 is the sheet range "START:END"
Cell BE$74 is the column "LZ"
Cell W$92 is the row "153"
The formula works if entered directly as a formula without referencing the cells listed above
but not as I have attempted. I have tried to search through other posts in the forum but haven't been able to figure out what is causing the #REF! error.
Any assistance would be very much appreciated!
I would like to create a sum formula referencing cell ranges that are constructed from multiple cells (one cell for the sheet range, one for the column, on for the row). In the example pictured below I am attempting to sum a specific cell from all of the sheets between the "START" sheet and the "END" sheet. I would like to use cell references to define the ranges as I will have a large number of formulas built up in this way and it would be easier to control the structure. The sheet range will be static, but the columns and rows will change. I have attempted to use the following formula which results in a #REF! error:
Excel Formula:
=(SUM(INDIRECT($W$101&BE$74&W$92)))
Cell $W$101 is the sheet range "START:END"
Cell BE$74 is the column "LZ"
Cell W$92 is the row "153"
The formula works if entered directly as a formula without referencing the cells listed above
Excel Formula:
=SUM(START:END!LZ$153)
Any assistance would be very much appreciated!