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?

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.



K J wrote:
>
> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>

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

On Jun 19, 12:44 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:
> 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

I think this worked. Thanks.

On Jun 19, 12:40 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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.

K J wrote:

> > Is there any way to change about 200 recurrences of the worksheet name
> > in links in one fell swoop?
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

I'd still try it again.

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

K J wrote:
>
> Yeah, I had that checked when it failed to work.
>
> On Jun 19, 12:44 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
> wrote:
> > 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

--

Dave Peterson