Changing the worksheet name in about 200 links / references

[K J]

Hello, Excel group.

I've run out of keyword ideas to look up the answer myself, so here I
come with a question.

I have to copy data out of a whole bunch rows in a worksheet and paste
it, row by row, into a different worksheet where the rows are in a
different order.

Then I have to do the same thing with a second worksheet.

And a third...

....And a twentieth.

Since each of these source worksheets have the data in the exact same
rows and columns among them, I was thinking that I could just make my
"different worksheet" pull data, cell by cell, from whatever worksheet
I was using, save a 2nd copy where I've copied & "paste specialed" the
numbers as values only instead of links to the source worksheet, and
revert my "different worksheet" back to links, change the name of the
"source worksheet" in all the links, and do it again.

The only problem is changing the name of the "source worksheet" in all
these cells. Find & Replace doesn't seem to do it. (I tried changing
the two I'd put in so far, ='Human Resources'!B10 & ='Human Resources'!
B2 to ='Accounting'!B10 & ='Accounting'!B2, respectively, with Find &
Replace and it just didn't work.)

I thought, "Well, I'll just have it pull the name from a blank cell
I've got here at the top, where I'll type it once--like B2."

So I set B2 to say "Human Resources" and then started changing one of
my cells to say =B2!B10
I hit enter and...it didn't like that. It opened some sort of file-
opening dialogue box.

Is there any way to change about 200 recurrences of the worksheet name
in links in one fell swoop?

 

[Dave Peterson]

I'm surprised that the edit|Replace didn't work for you.

You may want to experiment with a smaller test workbook.

But you can use:
=indirect("'" & $b$2 & "'!b10")

To retrieve the value in B10 of the worksheet whose name is in B2.

 

[Bernie Deitrick]

K J,

Select the cells with the formulas, and press Ctrl-H to bring up the replace dialog.

Replace

Human Resources

with

Accounting

and make sure that in your options you have selected Look In Formulas.

HTH,
Bernie
MS Excel MVP

 

[K J]

Yeah, I had that checked when it failed to work.

 

[K J]

I think this worked. Thanks.

I'm surprised that the edit|Replace didn't work for you.

You may want to experiment with a smaller test workbook.

But you can use:
=indirect("'" & $b$2 & "'!b10")

To retrieve the value in B10 of the worksheet whose name is in B2.


--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[Dave Peterson]

I'd still try it again.

I bet you made a small mistake for it to fail.