rename items in formulas

Guys,

I have 52 rows with 7 columns in each row that need to renamed by hand and it is a pain in the but.

Is there a way to do the following

coloum A of each row is labeled WEEK XX with XX being a number and letter.

for example
Week 1a
Week 1b
Week 2a
Week 2b etc etc up to 26a and 26b

Now because the way my cell formulas were copied they are all mislabeled.So it will take forever by hand to do this. Can I come up with a micro to run and do it easy.

In Row 10 for example Column A is WEEK 1a

so in the 7 formulas in that row I need the existing sheet name changed to WEEK 1a.
for example
column D has ='Week 6b'!I$11
that needs to be ='Week 1a'!I$11


to sum it up simple in ROW 10 every where where WEEK xx is it needs to be = to 10A which is Week 1a.

Matt

You could change D10, for example, to

=INDIRECT("'" & A10 & "'!I$11")

... then the reference would change automatically whenever yo change col A. The downside is that the formula is volatile, meaning it will recalculate each time anything calculates.

=8 - indirect("'" & a1 & "'$c$11")

=iferror(indirect("'" & a1 & "'!u$12"), "")

=iferror(indirect("'" & a1 & "'x$11") / indirect("'" & a1 & "'!u$12"), 0)


... changing A1 approriately.

... changing A1 appropriately.

... meaning change it to the row in which the formula appears. If it appears in row 10, change it to A10.

Do you mean?

=8-(INDIRECT("'"&INDEX(A:A,ROW())&"'!$C$11"))

I just replace your "sheet name" with INDEX(A:A,ROW())

which indexes column A, and set the row number to the current row...

so


=IFERROR(INDIRECT("'"&INDEX(A:A,ROW())&"'!U$12"),"")

and

=IFERROR(INDIRECT("'"&INDEX(A:A,ROW())&"'!X$11")/INDIRECT("'"&INDEX(A:A,ROW())&"'!U$12"),0)