Expand Rows with References

New Contributor

I have a file with 52 sheets...yes one for every week.

I am doing a yearly recap and I want to total or average the data from each weekly sheet onto a yearly recap. 

I have the basic recap done, but I can't remember how to expand the number of rows and keep the appropriate references.

 

For example I have a   row with the formula ='Week 30'!$B$139. If I expand that to another row I want to change it to week 31, but maintain the other reference data.

 

I've done this before but I can't remember how to do it.

 

Thanks.

3 Replies

@JonHunt 

Let's say your formula referring to Week 30 is in a cell in row 34.

 

=INDIRECT("'Week " & ROW()-4 & "1!B139")

 

In row 34, ROW() = 34, so we use ROW()-4 to get the number 30.

Thanks, but I don't follow that.

 

I have 52 sheets.

 

On the summary sheet I pull data from each of the 52 sheets.

 

If I just highlight the row and pull down the row, then it copies the data from Week 30 instead of the data from week 31.

 

So the data for week 31 is not in a sell but in a shed labeled Week 31.@Hans Vogelaar 

@JonHunt 

My apologies, I had a typo. It whoyld have been

 

=INDIRECT("'Week " & ROW()-4 & "'!B139")

 

Let's say you have this formula in B34. ROW()-4 evaluates to 34-4 = 30, so "'Week " & ROW()-4 & "'!B139" evaluates to

"'Week 30'!B139

INDIRECT converts this to a cell reference to B139 on Week 30.

With the same formula in B35, ROW()-4 evaluates to 35-4 = 31, so "'Week " & ROW()-4 & "'!B139" evaluates to

"'Week 31'!B139

INDIRECT converts this to a cell reference to B139 on Week 31.

Etc.