rename items in formulas

**rbpd5015** · 04-09-2010, 03:51 PM

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

**shg** · 04-09-2010, 04:01 PM

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.

**rbpd5015** · 04-09-2010, 04:06 PM

How do I use Indirect for these

=8-('Week 1a'!$C$11)

=IFERROR('Week 1a'!U$12,"")

=IFERROR('Week 1a'!X$11/'Week 1a'!U$12,0)

Thanks,

Matt

**shg** · 04-09-2010, 04:11 PM

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

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

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

... changing A1 approriately.

**rbpd5015** · 04-09-2010, 04:14 PM

you are saying a1 did you me A10?

I am getting an invalid cell reference.

Matt

**shg** · 04-09-2010, 04:16 PM

... changing A1 appropriately.

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

**rbpd5015** · 04-09-2010, 04:16 PM

This doesnt help because A1 does not stay the same.

**rbpd5015** · 04-09-2010, 04:19 PM

ok

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

this is the formula you gave me no

A10 is Week 1a
A11 is Week 1b
A12 is Week 2a
A13 is Week 2b
A14 is Week 3a and so on

NOW

I need this formula

=8-('Week 1a'!$C$11)
where Week 1a is to be replaced by the value in the current rows A column

I think I was confusing you before

that's the same with these 2 also

=IFERROR('Week 1a'!U$12,"")

=IFERROR('Week 1a'!X$11/'Week 1a'!U$12,0)

Matt

**NBVC** · 04-09-2010, 04:24 PM

Do you mean?

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

**rbpd5015** · 04-09-2010, 04:26 PM

NBVC,

That works GREAT can you care to share with me how that formula breaks down. I am here to learn just not find answers.

If I learn how you did it I can do it for

=IFERROR('Week 1a'!U$12,"")

=IFERROR('Week 1a'!X$11/'Week 1a'!U$12,0)

Matt

**NBVC** · 04-09-2010, 05:08 PM

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)