I have a spreadsheet that will eventually contain 52 sheets(one for each week) On each sheet there is a cell with a percentage in it. I would like to copy this cell to a summary sheet so that it can be graphed.
The formula on the summary sheet is =Week1!J7. Copying the data from cell J7 in sheet week 1. Is there a way to autofill this formula but increase the sheet number? eg =Week2!J7.
Your best bet is to have a listing of the sheets on your summary sheets and use a forumla that references them
A B
Week1 =indirect(A1&"!J7")
Week2 =indirect(A2&"!J7")
Week3 =indirect(A3&"!J7")
You can drag down the indirect function and it will update based on the sheet name in column A. Press f9 to refresh after you've dragged them. Sure it requires a "helper" column, but it will work fine
So type Week1 in A1 and drag it down to A52, then type =indirect(A1&"!J7") in B1 and drag it down to B52. Then press F9
To do this without a helper column, just use this formula in A1 and fill down.
=INDIRECT("Week"&ROW()&"!$J$7")
If you are starting in a different row, change the ROW() sub-string to match the value to 1 in the first entry. For example, if you start in A2 instead, you would use the following.
=INDIRECT("Week"&ROW()-1&"!$J$7")
EDIT (a year later):
Better yet, use the formula below regardless of what your starting row is.
=INDIRECT("Week"&ROWS($1:1)&"!$J$7")
You should type the first two, then drag the auto-fill handle (the little square at the bottom-right of any selection outline in Excel) to fill the rest of the items, it should detect the incrementing digit.
