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.

 

[Joe4]

Welcome to the Board!

Can you tell me exactly what cell address your first formula is being placed in?

=SUMIF('01'!F5:F54,Daily_Detail!V2,'01'!E5:E54)

We can use the ROW() function from the cell that it is placed in to help us do what you want.
So if you let us know what it is, we can help you come up with the formula.

 

[ronb16]

J3 is the first cell I would start in.

 

[Joe4]

OK, try placing this formula in cell J3 and then copying to your other cells:
=SUMIF(INDIRECT("'" & TEXT(ROW()-2,"00") & "'!$F$5:$F$54"),Daily_Detail!$V$2,'01'!$E$5:$E$54)

Note that we are using a formula to build the range we want to reference.
However, when you do that, Excel thinks it is a string, not a range. We can apply the INDIRECT function on it to convert that string to a valid range.

If you want to see what the formula is actually building, just placing this formula (the inside part of the INDIRECT formula) in row 3 of any column and then copy down the rows and watch what it does:
="'" & TEXT(ROW()-2,"00") & "'!$F$5:$F$54"

So what we are doing is using a formula that dynamically creates the range:
'01'!$F$5:$F$54
and will increment the number as it goes down rows.

 

[ronb16]

OK, try placing this formula in cell J3 and then copying to your other cells:
=SUMIF(INDIRECT("'" & TEXT(ROW()-2,"00") & "'!$F$5:$F$54"),Daily_Detail!$V$2,'01'!$E$5:$E$54)

Note that we are using a formula to build the range we want to reference.
However, when you do that, Excel thinks it is a string, not a range. We can apply the INDIRECT function on it to convert that string to a valid range.

If you want to see what the formula is actually building, just placing this formula (the inside part of the INDIRECT formula) in row 3 of any column and then copy down the rows and watch what it does:
="'" & TEXT(ROW()-2,"00") & "'!$F$5:$F$54"

So what we are doing is using a formula that dynamically creates the range:
'01'!$F$5:$F$54
and will increment the number as it goes down rows.

Hi Joe, I appreciate your help with this. I copy and pasted the formula you gave me and copied it down the row. Here is what I ended up with:

Did I do something wrong?

 

[Joe4]

It looks like it is showing the formula instead of the results that the formula should return.
Do you have the column Format set to "Text" instead of "General" or "Number"?
If so, change the format of the column to "General" and re-enter the formula.

Also, check to make sure that you do not have any extra spaces (or other special characters) at the beginning or end of that formula entry (that would coerce the entry to text also).

 

[ronb16]

It looks like it is showing the formula instead of the results that the formula should return.
Do you have the column Format set to "Text" instead of "General" or "Number"?
If so, change the format of the column to "General" and re-enter the formula.

Also, check to make sure that you do not have any extra spaces (or other special characters) at the beginning or end of that formula entry (that would coerce the entry to text also).

I had selected to show formula thinking it would better help you understand. I was not getting the results I had expected when I first used your formula.

 

[Joe4]

If you put your original formula:

=SUMIF('01'!F5:F54,Daily_Detail!V2,'01'!E5:E54)

and my formula:

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

side-by-side in two columns anywhere in row 3, do they return the same value?

If not, place this formula, in any column on row 3 and tell me what it returns:

="'" & TEXT(ROW()-2,"00") & "'!$F$5:$F$54"

 

[ronb16]

If you put your original formula:

=SUMIF('01'!F5:F54,Daily_Detail!V2,'01'!E5:E54)

and my formula:

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

side-by-side in two columns anywhere in row 3, do they return the same value?

If not, place this formula, in any column on row 3 and tell me what it returns:

="'" & TEXT(ROW()-2,"00") & "'!$F$5:$F$54"

Yes the values are the same. Maybe I am not properly asking the question. In the first screen shot is a summary of purchases for each day (Separate Pages in workbook)

The second screenshot is a single day page. I appreciate you taking your time to try and help me navigate this, I do not have a lot of excel experience and I'm trying to modify this document to make it easier to use and understand.

 

[Joe4]

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.