Hi all,

I have a set of about 250spreadsheet files each contaning 4 individual
sheets. I now need to add an additional sheet to each file which will
contain cell references to cells on other sheets of that file.

I intended to add the sheet and create the cell references in one of
the files and simply copy that to the the added sheet in each of the
other files, but when doing so I found that the reference to the file
copied from was included in the cell reference in file I pasted to.

Example -

Copying from a file named first file and pasting to a file named
second file results in a cell reference like ='[first file.xls]sheet1!
$c$15 in second file. I need the pasted result to be =sheet1!$c$15.

Is there some way this can be accomplished or is there some other way
to go about this project?

Thanks for any advice

Crownman

Before you copy the worksheet into the new workbook, change all the formulas to
text.

Select all the cells
edit|Replace
what: = (equal sign)
with: $$$$$=
replace all

Copy the worksheet into the receiving workbook(s) and reverse the change.

Alternatively, you can let the links get created, then...
Edit|Links|change source
to point at the new workbook.

Crownman wrote:
>
> Hi all,
>
> I have a set of about 250spreadsheet files each contaning 4 individual
> sheets. I now need to add an additional sheet to each file which will
> contain cell references to cells on other sheets of that file.
>
> I intended to add the sheet and create the cell references in one of
> the files and simply copy that to the the added sheet in each of the
> other files, but when doing so I found that the reference to the file
> copied from was included in the cell reference in file I pasted to.
>
> Example -
>
> Copying from a file named first file and pasting to a file named
> second file results in a cell reference like ='[first file.xls]sheet1!
> $c$15 in second file. I need the pasted result to be =sheet1!$c$15.
>
> Is there some way this can be accomplished or is there some other way
> to go about this project?
>
> Thanks for any advice
>
> Crownman

--

Dave Peterson

I think I"d do this with VBA. First, there is a bit of housekeeping that
will need to be done. I'd put all of the 250 spreadsheet files in one
folder. Unfortunately, I have a lot of pieces of the code that you'd need
on another computer and it had a GREEN SCREEN today so I can't get to it.
If you haven't gotten a response by the time I get my other system back up,
I'll send one then.



"Crownman" wrote:

> Hi all,
>
> I have a set of about 250spreadsheet files each contaning 4 individual
> sheets. I now need to add an additional sheet to each file which will
> contain cell references to cells on other sheets of that file.
>
> I intended to add the sheet and create the cell references in one of
> the files and simply copy that to the the added sheet in each of the
> other files, but when doing so I found that the reference to the file
> copied from was included in the cell reference in file I pasted to.
>
> Example -
>
> Copying from a file named first file and pasting to a file named
> second file results in a cell reference like ='[first file.xls]sheet1!
> $c$15 in second file. I need the pasted result to be =sheet1!$c$15.
>
> Is there some way this can be accomplished or is there some other way
> to go about this project?
>
> Thanks for any advice
>
> Crownman
>
>

On Jun 19, 5:35 pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:
> I think I"d do this with VBA. First, there is a bit of housekeeping that
> will need to be done. I'd put all of the 250 spreadsheet files in one
> folder. Unfortunately, I have a lot of pieces of the code that you'd need
> on another computer and it had a GREEN SCREEN today so I can't get to it.
> If you haven't gotten a response by the time I get my other system back up,
> I'll send one then.
>
>
>
> "Crownman" wrote:
> > Hi all,
>
> > I have a set of about 250spreadsheet files each contaning 4 individual
> > sheets. I now need to add an additional sheet to each file which will
> > contain cell references to cells on other sheets of that file.
>
> > I intended to add the sheet and create the cell references in one of
> > the files and simply copy that to the the added sheet in each of the
> > other files, but when doing so I found that the reference to the file
> > copied from was included in the cell reference in file I pasted to.
>
> > Example -
>
> > Copying from a file named first file and pasting to a file named
> > second file results in a cell reference like ='[first file.xls]sheet1!
> > $c$15 in second file. I need the pasted result to be =sheet1!$c$15.
>
> > Is there some way this can be accomplished or is there some other way
> > to go about this project?
>
> > Thanks for any advice
>
> > Crownman- Hide quoted text -
>
> - Show quoted text -


Barb:

I haven't had a chance yet to test out Dave's suggested solutions, so
I would love to see your ideas as well.

Crownman

On Jun 19, 2:35 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Before you copy the worksheet into the new workbook, change all the formulas to
> text.
>
> Select all the cells
> edit|Replace
> what: = (equal sign)
> with: $$$$$=
> replace all
>
> Copy the worksheet into the receiving workbook(s) and reverse the change.
>
> Alternatively, you can let the links get created, then...
> Edit|Links|change source
> to point at the new workbook.
>
>
>
>
>
> Crownman wrote:
>
> > Hi all,
>
> > I have a set of about 250spreadsheet files each contaning 4 individual
> > sheets. I now need to add an additional sheet to each file which will
> > contain cell references to cells on other sheets of that file.
>
> > I intended to add the sheet and create the cell references in one of
> > the files and simply copy that to the the added sheet in each of the
> > other files, but when doing so I found that the reference to the file
> > copied from was included in the cell reference in file I pasted to.
>
> > Example -
>
> > Copying from a file named first file and pasting to a file named
> > second file results in a cell reference like ='[first file.xls]sheet1!
> > $c$15 in second file. I need the pasted result to be =sheet1!$c$15.
>
> > Is there some way this can be accomplished or is there some other way
> > to go about this project?
>
> > Thanks for any advice
>
> > Crownman
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Dave:

I tried a quick test on your alternate solution and it looked like it
will work just fine. Thanks so much for your help.

Crownman