Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Absoluet Reference
Everytime I link an cell to another workbook, it automatically puts in an
absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. Thanks |
#2
|
|||
|
|||
You need that reference or else Excel won't know where to link to.
There is one option you could use, which is to copy the tab from the external sheet into your current one. To do that, make sure both worksheets are open, go to the workbook with the tab you want to copy, right click on it, and select "Move or Copy". Then, choose the worksheet you want to copy the tab to and make sure "Create a Copy" is selected. Hope that helps -- Regards, David Billigmeier "ChrisK" wrote: Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. Thanks |
#3
|
|||
|
|||
Thanks David for the reply - but I'm not sure this answers my question. For example if I link to another worksheet (+2006 Budget Worksheet - Marketing.xls]2006 MONTHLY'!$C$24) It automatically puts the "$" absolute reference in there. If I have many links and I want to copy it down, I have to remove the "$" which can take a lot of time given the amount of files I am linking to . Is there a way to link an external file without getting the "$" symbol? Thanks "David Billigmeier" wrote: You need that reference or else Excel won't know where to link to. There is one option you could use, which is to copy the tab from the external sheet into your current one. To do that, make sure both worksheets are open, go to the workbook with the tab you want to copy, right click on it, and select "Move or Copy". Then, choose the worksheet you want to copy the tab to and make sure "Create a Copy" is selected. Hope that helps -- Regards, David Billigmeier "ChrisK" wrote: Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. Thanks |
#4
|
|||
|
|||
*Exactly* how are you creating your link?
= sign and navigating? Copy & Paste Spec - Link? How? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ChrisK" wrote in message ... Thanks David for the reply - but I'm not sure this answers my question. For example if I link to another worksheet (+2006 Budget Worksheet - Marketing.xls]2006 MONTHLY'!$C$24) It automatically puts the "$" absolute reference in there. If I have many links and I want to copy it down, I have to remove the "$" which can take a lot of time given the amount of files I am linking to . Is there a way to link an external file without getting the "$" symbol? Thanks "David Billigmeier" wrote: You need that reference or else Excel won't know where to link to. There is one option you could use, which is to copy the tab from the external sheet into your current one. To do that, make sure both worksheets are open, go to the workbook with the tab you want to copy, right click on it, and select "Move or Copy". Then, choose the worksheet you want to copy the tab to and make sure "Create a Copy" is selected. Hope that helps -- Regards, David Billigmeier "ChrisK" wrote: Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. Thanks |
#5
|
|||
|
|||
To get my link I wither hit the "=" or the "+" and find the cell I want to
link to. If I use an external file it will put the absolute reference in there automatically. Then if I want to copy that link down I have to remove the absolute reference for each link. I'm just looking for a shortcut. Thanks "RagDyer" wrote: *Exactly* how are you creating your link? = sign and navigating? Copy & Paste Spec - Link? How? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ChrisK" wrote in message ... Thanks David for the reply - but I'm not sure this answers my question. For example if I link to another worksheet (+2006 Budget Worksheet - Marketing.xls]2006 MONTHLY'!$C$24) It automatically puts the "$" absolute reference in there. If I have many links and I want to copy it down, I have to remove the "$" which can take a lot of time given the amount of files I am linking to . Is there a way to link an external file without getting the "$" symbol? Thanks "David Billigmeier" wrote: You need that reference or else Excel won't know where to link to. There is one option you could use, which is to copy the tab from the external sheet into your current one. To do that, make sure both worksheets are open, go to the workbook with the tab you want to copy, right click on it, and select "Move or Copy". Then, choose the worksheet you want to copy the tab to and make sure "Create a Copy" is selected. Hope that helps -- Regards, David Billigmeier "ChrisK" wrote: Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. Thanks |
#6
|
|||
|
|||
ChrisK wrote...
Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. This is core Excel functionality. External references are just treated differently than internal references. No way to change this. Only possible to learn to live with it. |
#7
|
|||
|
|||
Try this:
Enter the = sign in the target cell, Navigate to the source cell, Click in that cell and you see the marquee (marching ants) around it, while the formula bar displays the path with the absolute addresses. Nothing looks selected in the formula bar ... BUT ... NOW ... hit <F4 3 times, and you'll see the references change to relative. *NOW* hit <Enter. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ChrisK" wrote in message ... To get my link I wither hit the "=" or the "+" and find the cell I want to link to. If I use an external file it will put the absolute reference in there automatically. Then if I want to copy that link down I have to remove the absolute reference for each link. I'm just looking for a shortcut. Thanks "RagDyer" wrote: *Exactly* how are you creating your link? = sign and navigating? Copy & Paste Spec - Link? How? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "ChrisK" wrote in message ... Thanks David for the reply - but I'm not sure this answers my question. For example if I link to another worksheet (+2006 Budget Worksheet - Marketing.xls]2006 MONTHLY'!$C$24) It automatically puts the "$" absolute reference in there. If I have many links and I want to copy it down, I have to remove the "$" which can take a lot of time given the amount of files I am linking to . Is there a way to link an external file without getting the "$" symbol? Thanks "David Billigmeier" wrote: You need that reference or else Excel won't know where to link to. There is one option you could use, which is to copy the tab from the external sheet into your current one. To do that, make sure both worksheets are open, go to the workbook with the tab you want to copy, right click on it, and select "Move or Copy". Then, choose the worksheet you want to copy the tab to and make sure "Create a Copy" is selected. Hope that helps -- Regards, David Billigmeier "ChrisK" wrote: Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. Thanks |
#8
|
|||
|
|||
I like David's idea, but I think you should move the worksheet(s), then do all
your formulas, then move the worksheet(s) back. Then the links will adjust to the correct workbook, too. ChrisK wrote: Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. Thanks -- Dave Peterson |
#9
|
|||
|
|||
I was just searching for this information. I thought that was the answer. The
answer given by another reader to hit F4 three times is correct, but not effective if there are multiple references in the same cell. It would be great to have the default for links to be relative instead of absolute. Thanks for the information, everyone. "Harlan Grove" wrote: ChrisK wrote... Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. This is core Excel functionality. External references are just treated differently than internal references. No way to change this. Only possible to learn to live with it. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absoluet Reference
I know the post is old, but I could not find an answer elsewhere in the
forum. After you paste the data, go to Edit, Find and Replace, then find '[*] and replace with ' . You need to select Within -Workbook and Look In -Formulas. The Replace All. This will replace all instances of cells referencing the linked absolute file, indicated by the [filename.xls] reference. "ChrisK" wrote: Everytime I link an cell to another workbook, it automatically puts in an absolute reference. Is there a setting I can change so I do not have absolute references appear. This does not happen when you link a cell within the same sheet only external worksheets. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
how do I format a cell reference to move as source changes | Excel Worksheet Functions | |||
a cell reference in a 3d reference | Excel Worksheet Functions | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
Cell Reference Math | Excel Worksheet Functions |