Copy Down

[Yrrep]

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)

 

[Ashish Mathur]

Hi,

Try this

=if(indirect(A2&"!L$3")=0,"",indirect(A2&"!L$3"))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jarek Kujawa]

in A1:

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

adjust 11 to suit

 

[Yrrep]

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



in A1:

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

adjust 11 to suit

 

[Dave Peterson]

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.