Forum Discussion
Expand Rows with References
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.HansVogelaar
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.