Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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

share|improve this answer
    
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")
share|improve this answer

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.