Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying relative reference across worksheets | Excel Discussion (Misc queries) | |||
copying a column reference into a row | Excel Discussion (Misc queries) | |||
copying a reference | Excel Worksheet Functions | |||
Copying a mixed reference formula | Excel Discussion (Misc queries) | |||
copying a formula, the reference adjusts, but the result does not | Excel Discussion (Misc queries) |