Summing cells across different pages, where the pages to add are determined by a cell (INDIRECT question)

[Joe94]

I have different tabs called Wk1, Wk2, Wk3, etc. which are all exactly the same table where people will enter weekly data. I want to have a YTD tab which is exactly the same table and adds the data from Week 1 to Week X, where X is a number in cell I1. So if I1=4, the formula in cell B5 will add B5 from each of the tabs Wk1, Wk2, Wk3, Wk4.

Could anyone help me? This seems like a simple mixture of SUM and INDIRECT formulas but I keep getting errors. Also as I need to copy the formula over a whole table, I would like B5 to not be fixed so I can just copy and paste it over the whole table. Thanks

 

[steve the fish]

This is the way id go. Create a named range called 'Weeks' with all of your sheet names in it. Then this should work:

=SUMPRODUCT(SUM(INDIRECT("'"&INDEX(Weeks,1):INDEX(Weeks,$I$1)&"'!"&ADDRESS(ROW(),COLUMN()))))

 

[Joe94]

This worked perfectly, thank you so much! If you don't mind could you please explain how SUM and SUMPRODUCT are working together in this example? I think I understand how most of the formula works by using Evaluate Formula but I am not sure why 2 different SUM equations are required.
Again, thanks a lot for the help.

 

[steve the fish]

Its just being used to force excel to use the array of sheets so therefore array of cells. If you dont use it like that it produces erroneous results. This for instance will only use the first sheet/cell in the Weeks named range

=SUM(INDIRECT("'"&INDEX(Weeks,1):INDEX(Weeks,$I$1)&"'!"&ADDRESS(ROW(),COLUMN())))

so then wrapping that with sumproduct makes it use all of the sheets. If you use sumproduct without a sum it similarly fails.