Sum referenced cells in Sheet range using SUM(INDIRECT()) #REF! ERROR

[chappy]

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:

=(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

=SUM(START:END!LZ$153)

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!

 

[Fluff]

Ok, how about

=LET(v,VSTACK(Start:End!A1:LZ300),SUM(INDEX(v,SEQUENCE(ROWS(v)/300,,W92,300),BE74)))

Change BE74 to the cell that has the column number, so for col LZ it would have 338

 

[chappy]

Thanks very much for your help. I will test a bit more using your solution.

 

[Fluff]

You're welcome & thanks for the feedback.