Copy without reference to original file

[Crownman]

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]

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.

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

 

[Guest]

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]

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.

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

 

[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.

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