copying 3D reference from one file to another
 

I have a file called Book1. In cell C1 on Sheet2 there is an equation that
reads:

=Sheet1!A1+10

I have another file called Book2. When I copy the above equation into cell
C1 on Sheet2 of Book2, it pastes in the following:

=[Book2]Sheet1!A1+10

So Excel wants to make a reference back to Sheet1!A1 in the original file.

I want to copy the equation over to the new book, but just have it make
reference to Sheet1!A1 in the new book, not back to the original file. It
seems like the reference to the sheet is absolute by default, but I want it
to be relative.

Is there a way to get Excel to copy a sheet reference from one file to
another without making reference back to the original file?

Thanks for any help.

Correction
 

Pardon me, there was a typo in the way I phrased the original question. Here
is the update:

I have a file called Book1. In cell C1 on Sheet2 there is an equation that
reads:

=Sheet1!A1+10

I have another file called Book2. When I copy the above equation into cell
C1 on Sheet2 of Book2, it pastes in the following:

=[Book1]Sheet1!A1+10

So Excel wants to make a reference back to Sheet1!A1 in the original file.

I want to copy the equation over to the new book, but just have it make
reference to Sheet1!A1 in the new book, not back to the original file. It
seems like the reference to the sheet is absolute by default, but I want it
to be relative.

Is there a way to get Excel to copy a sheet reference from one file to
another without making reference back to the original file?

Thanks for any help.

Correction
 

Try this macro:

Sub formulaize()
Dim r As Range
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("Book1.xls")
Set wb2 = Workbooks("Book2.xls")

wb1.Activate
Sheets("Sheet2").Activate
MsgBox (" ")
Dim s As String
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
addy = r.Address
s = r.Formula
wb2.Sheets("Sheet2").Range(addy).Formula = s
End If
Next
End Sub

--
Gary''s Student - gsnu200906


"Eric" wrote:

Pardon me, there was a typo in the way I phrased the original question. Here
is the update:

I have a file called Book1. In cell C1 on Sheet2 there is an equation that
reads:

=Sheet1!A1+10

I have another file called Book2. When I copy the above equation into cell
C1 on Sheet2 of Book2, it pastes in the following:

=[Book1]Sheet1!A1+10

So Excel wants to make a reference back to Sheet1!A1 in the original file.

I want to copy the equation over to the new book, but just have it make
reference to Sheet1!A1 in the new book, not back to the original file. It
seems like the reference to the sheet is absolute by default, but I want it
to be relative.

Is there a way to get Excel to copy a sheet reference from one file to
another without making reference back to the original file?

Thanks for any help.

Correction
 

Thanks for the help. I was hoping there was an Options setting change I could
use to change how Excel copies sheet references from one file to another. Is
a macro the only way to handle this?

Thanks - Eric

"Gary''s Student" wrote:

Try this macro:

Sub formulaize()
Dim r As Range
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("Book1.xls")
Set wb2 = Workbooks("Book2.xls")

wb1.Activate
Sheets("Sheet2").Activate
MsgBox (" ")
Dim s As String
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
addy = r.Address
s = r.Formula
wb2.Sheets("Sheet2").Range(addy).Formula = s
End If
Next
End Sub

--
Gary''s Student - gsnu200906


"Eric" wrote:

Pardon me, there was a typo in the way I phrased the original question. Here
is the update:

I have a file called Book1. In cell C1 on Sheet2 there is an equation that
reads:

=Sheet1!A1+10

I have another file called Book2. When I copy the above equation into cell
C1 on Sheet2 of Book2, it pastes in the following:

=[Book1]Sheet1!A1+10

So Excel wants to make a reference back to Sheet1!A1 in the original file.

I want to copy the equation over to the new book, but just have it make
reference to Sheet1!A1 in the new book, not back to the original file. It
seems like the reference to the sheet is absolute by default, but I want it
to be relative.

Is there a way to get Excel to copy a sheet reference from one file to
another without making reference back to the original file?

Thanks for any help.

copying 3D reference from one file to another
 

One way............

Copy the formula directly from the formula bar.

Paste that to new workbook.

Another way for multiple formulas..................

EditReplace

What: =

With: ^^^

Replace all

Copy and paste then reverse the editreplace.


Gord Dibben MS Excel MVP

On Mon, 5 Oct 2009 12:58:02 -0700, Eric
wrote:

I have a file called Book1. In cell C1 on Sheet2 there is an equation that
reads:

=Sheet1!A1+10

I have another file called Book2. When I copy the above equation into cell
C1 on Sheet2 of Book2, it pastes in the following:

=[Book2]Sheet1!A1+10

So Excel wants to make a reference back to Sheet1!A1 in the original file.

I want to copy the equation over to the new book, but just have it make
reference to Sheet1!A1 in the new book, not back to the original file. It
seems like the reference to the sheet is absolute by default, but I want it
to be relative.

Is there a way to get Excel to copy a sheet reference from one file to
another without making reference back to the original file?

Thanks for any help.