I like this technique:

Put this formula in C4:
="$$$='Week"&ROW()-3&"'!L3"

Drag down as far as you need (51 more rows).

Then convert these formulas to values.

Then do an edit|Replace
what: $$$
with: (leave blank)
replace all

If you're not sure you created the correct formula, you'll want to test it on
just a few cells. If you've made a mistake, you'll be prompted to select the
name of the workbook that contains the correct worksheet.

And if you've done this 52 times, that means 52 dismissed dialogs or killing
excel.




Yrrep wrote:

Neither one worked

Will try to make it easier

Have 52 sheets "week 1 through week 52"

Box L3 has a number averaged on each sheet

Another sheet has the formula =Week1!L3 starting in column C4

I want to copy down so the next row below says week2 l3 and so on all the
way down

TIA

"Jarek Kujawa" wrote in message
...
in A1:

=IF(INDIRECT("Week"&(ROW()+11)&"!L$3")=0,"",INDIRE CT("Week"&(ROW()+11)
&"!L$3"))

adjust 11 to suit

On 18 Mar, 14:10, "Yrrep" wrote:
I want to copy the formula below down the sheet so that the week
increases. i.e. week 12 week 13 etc. (The week represents a different
named
sheet)

=IF(Week12!L$3=0,"",Week12!L$3)

--

Dave Peterson