# Update a summary sheet in Excel

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

-
Works perfect. Thank you – Dan Feb 17 '12 at 19:24
@Dan If it works for you mark the question answered by clicking the checkmark next to the answer that worked for you – Raystafarian Feb 17 '12 at 19:25

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.

-
Doing that increases the cell number but on the same Week1 sheet.eg =Week1!J8,9,10 ect. I would like to change the actual sheet number.eg Week2!J7 – Dan Feb 17 '12 at 19:01