Help sequencing same formula in rows and columns referencing the same cells on different pages of the workbook.

[ronb16]

Hi Excel Geniuses,

I am creating a checkbook with dropdown a drop down menu for different GL Codes. I would like a summary of each GL Code on a single page. I figured out my formula, but I'm having trouble sequencing the formula to reference the same cells on each page and hope to not have to copy and paste each cell then manually change the page number or column number. Here is an example of what I'm trying to accomplish.

=SUMIF('01'!F5:F54,Daily_Detail!V2,'01'!E5:E54)	=SUMIF('01'!F5:F54,Daily_Detail!W2,'01'!E5:E54)	=SUMIF('01'!F5:F54,Daily_Detail!X2,'01'!E5:E54)
=SUMIF('02'!F5:F54,Daily_Detail!V2,'01'!E5:E54)	=SUMIF('02'!F5:F54,Daily_Detail!W2,'01'!E5:E54)	=SUMIF('02'!F5:F54,Daily_Detail!X2,'01'!E5:E54)
=SUMIF('03'!F5:F54,Daily_Detail!V2,'01'!E5:E54)	=SUMIF('03'!F5:F54,Daily_Detail!W2,'01'!E5:E54)	=SUMIF('03'!F5:F54,Daily_Detail!X2,'01'!E5:E54)
=SUMIF('04'!F5:F54,Daily_Detail!V2,'01'!E5:E54)	=SUMIF('04'!F5:F54,Daily_Detail!W2,'01'!E5:E54)	=SUMIF('04'!F5:F54,Daily_Detail!X2,'01'!E5:E54)

Any shortcut to help me not have to copy and paste then edit each formula would be greatly appreciated.

Thank you in advance.

 

[ronb16]

So, is it the first value is working properly, but the formulas do not work properly when copied?

If so, then I suspect there is an issue with the formulas in your original posting above.
Show me an example that doesn't work properly.
Tell me what cell address the formula is in, and show me your manual formula that returns the correct result for that particular cell.

Hi Joe,

So I have chosen to show the formulas here. All of the highlighted cells I copied the formula from J3 and pasted them into the other highlighted cells.
The Orange Highlighted cells I manually altered the the page number of column number to apply. The green highlighted are cells I have copied and pasted but not yet altered. My hope is to save me time and learn how to get the entire page to sequence similar to the orange highlighted cells.

 

[Joe4]

OK, I think we both missed some things. In your original post, you have a mistake in your formulas, that you don't have the second sheet reference in each formula changing, i.e.

And I missed that your formulas were shifting the columns on the Detail page (V, W, X), so try this variation:

=SUMIF(INDIRECT("'" & TEXT(ROW()-2,"00") & "'!$F$5:$F$54"),Daily_Detail!V$2,INDIRECT("'" & TEXT(ROW()-2,"00") & "'!$E$5:$E$54"))