Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link cell to another workbook
Hi everyone,
In Excel, how would I go about linking one cell to another cell in a different workbook? I understand I can type = then point it to the cell in the other workbook, however my problem is everytime the location of the file changes, the links mess up. For example, I have this command to link to a file called grid: =[grid.xlsx]Sheet1!$A$1 But when I want to move both the original file and the grid file to a different location (for example on a memory stick), the link fails and tries to point back to where the file was originally stored. Is it possible to use an apsolute refrerence for the link - like I would do with cells, so it always looks for the file 'grid.xlsx' in the folder? I really need your help on this one, I'm sure it's easy enough it's just I can't figure it out myself. Thanks, Tom :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link cell to another workbook
Actually it isn't all that easy to deal with ... or maybe it is.
After creating the link, when you close the file(s), the full address to the linked-to file is retained as part of the link. So later you move the files, or at least the linked-to file and your link quits working. The fix to that one, if it has already happened, is to use Edit -- Links from the Excel menu and simply point to the new location of the linked-to file. This is probably the route to take with your memory-stick example. Another "fix", if you'll open all files from their original location and then save them to a new location (as from one drive on a LAN to be saved in another folder/drive on the LAN) the links get patched up automatically by Excel. "Tom S" wrote: Hi everyone, In Excel, how would I go about linking one cell to another cell in a different workbook? I understand I can type = then point it to the cell in the other workbook, however my problem is everytime the location of the file changes, the links mess up. For example, I have this command to link to a file called grid: =[grid.xlsx]Sheet1!$A$1 But when I want to move both the original file and the grid file to a different location (for example on a memory stick), the link fails and tries to point back to where the file was originally stored. Is it possible to use an apsolute refrerence for the link - like I would do with cells, so it always looks for the file 'grid.xlsx' in the folder? I really need your help on this one, I'm sure it's easy enough it's just I can't figure it out myself. Thanks, Tom :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link cell to another workbook
Hi,
Many thanks for your reply, i'm really grateful. While that ghelps a lot, I'm still stuck in the sense that the file links to a number of different Excel spreadsheets, abnd it would be a real pain having to edit the links for every spreadsheet. Is there a way of changing the formula so it just looks for the file inside that folder? i.e. so it always uses the file grid.xlsx as long as they are all in the same folder? Thanks a lot, Tom. "JLatham" wrote: Actually it isn't all that easy to deal with ... or maybe it is. After creating the link, when you close the file(s), the full address to the linked-to file is retained as part of the link. So later you move the files, or at least the linked-to file and your link quits working. The fix to that one, if it has already happened, is to use Edit -- Links from the Excel menu and simply point to the new location of the linked-to file. This is probably the route to take with your memory-stick example. Another "fix", if you'll open all files from their original location and then save them to a new location (as from one drive on a LAN to be saved in another folder/drive on the LAN) the links get patched up automatically by Excel. "Tom S" wrote: Hi everyone, In Excel, how would I go about linking one cell to another cell in a different workbook? I understand I can type = then point it to the cell in the other workbook, however my problem is everytime the location of the file changes, the links mess up. For example, I have this command to link to a file called grid: =[grid.xlsx]Sheet1!$A$1 But when I want to move both the original file and the grid file to a different location (for example on a memory stick), the link fails and tries to point back to where the file was originally stored. Is it possible to use an apsolute refrerence for the link - like I would do with cells, so it always looks for the file 'grid.xlsx' in the folder? I really need your help on this one, I'm sure it's easy enough it's just I can't figure it out myself. Thanks, Tom :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link cell to another workbook
For the first part - and only if I understood the situation - I would suggest
to try the following. A Link, to another WB, is characterized by a Path & WB Name. Try to press CTRL+H (Search&Replace) Type the old path and the new path press 'options select "search in Workbook" "in Formulas". If I'm recall correctly the above procedure will update the links Micky "Tom S" wrote: Hi, Many thanks for your reply, i'm really grateful. While that ghelps a lot, I'm still stuck in the sense that the file links to a number of different Excel spreadsheets, abnd it would be a real pain having to edit the links for every spreadsheet. Is there a way of changing the formula so it just looks for the file inside that folder? i.e. so it always uses the file grid.xlsx as long as they are all in the same folder? Thanks a lot, Tom. "JLatham" wrote: Actually it isn't all that easy to deal with ... or maybe it is. After creating the link, when you close the file(s), the full address to the linked-to file is retained as part of the link. So later you move the files, or at least the linked-to file and your link quits working. The fix to that one, if it has already happened, is to use Edit -- Links from the Excel menu and simply point to the new location of the linked-to file. This is probably the route to take with your memory-stick example. Another "fix", if you'll open all files from their original location and then save them to a new location (as from one drive on a LAN to be saved in another folder/drive on the LAN) the links get patched up automatically by Excel. "Tom S" wrote: Hi everyone, In Excel, how would I go about linking one cell to another cell in a different workbook? I understand I can type = then point it to the cell in the other workbook, however my problem is everytime the location of the file changes, the links mess up. For example, I have this command to link to a file called grid: =[grid.xlsx]Sheet1!$A$1 But when I want to move both the original file and the grid file to a different location (for example on a memory stick), the link fails and tries to point back to where the file was originally stored. Is it possible to use an apsolute refrerence for the link - like I would do with cells, so it always looks for the file 'grid.xlsx' in the folder? I really need your help on this one, I'm sure it's easy enough it's just I can't figure it out myself. Thanks, Tom :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link cell to another workbook
After all selections push "Change All"
Micky "מיכאל (מיקי) אבידן" wrote: For the first part - and only if I understood the situation - I would suggest to try the following. A Link, to another WB, is characterized by a Path & WB Name. Try to press CTRL+H (Search&Replace) Type the old path and the new path press 'options select "search in Workbook" "in Formulas". If I'm recall correctly the above procedure will update the links Micky "Tom S" wrote: Hi, Many thanks for your reply, i'm really grateful. While that ghelps a lot, I'm still stuck in the sense that the file links to a number of different Excel spreadsheets, abnd it would be a real pain having to edit the links for every spreadsheet. Is there a way of changing the formula so it just looks for the file inside that folder? i.e. so it always uses the file grid.xlsx as long as they are all in the same folder? Thanks a lot, Tom. "JLatham" wrote: Actually it isn't all that easy to deal with ... or maybe it is. After creating the link, when you close the file(s), the full address to the linked-to file is retained as part of the link. So later you move the files, or at least the linked-to file and your link quits working. The fix to that one, if it has already happened, is to use Edit -- Links from the Excel menu and simply point to the new location of the linked-to file. This is probably the route to take with your memory-stick example. Another "fix", if you'll open all files from their original location and then save them to a new location (as from one drive on a LAN to be saved in another folder/drive on the LAN) the links get patched up automatically by Excel. "Tom S" wrote: Hi everyone, In Excel, how would I go about linking one cell to another cell in a different workbook? I understand I can type = then point it to the cell in the other workbook, however my problem is everytime the location of the file changes, the links mess up. For example, I have this command to link to a file called grid: =[grid.xlsx]Sheet1!$A$1 But when I want to move both the original file and the grid file to a different location (for example on a memory stick), the link fails and tries to point back to where the file was originally stored. Is it possible to use an apsolute refrerence for the link - like I would do with cells, so it always looks for the file 'grid.xlsx' in the folder? I really need your help on this one, I'm sure it's easy enough it's just I can't figure it out myself. Thanks, Tom :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link cell to another workbook
Now that I know you have links to multiple workbooks, not just one, I would
say that Mickey's suggestion is probably the way to go. Since when you use a memory stick the only thing that should change would be the drive letter, there's not even much typing to be done. "Tom S" wrote: Hi, Many thanks for your reply, i'm really grateful. While that ghelps a lot, I'm still stuck in the sense that the file links to a number of different Excel spreadsheets, abnd it would be a real pain having to edit the links for every spreadsheet. Is there a way of changing the formula so it just looks for the file inside that folder? i.e. so it always uses the file grid.xlsx as long as they are all in the same folder? Thanks a lot, Tom. "JLatham" wrote: Actually it isn't all that easy to deal with ... or maybe it is. After creating the link, when you close the file(s), the full address to the linked-to file is retained as part of the link. So later you move the files, or at least the linked-to file and your link quits working. The fix to that one, if it has already happened, is to use Edit -- Links from the Excel menu and simply point to the new location of the linked-to file. This is probably the route to take with your memory-stick example. Another "fix", if you'll open all files from their original location and then save them to a new location (as from one drive on a LAN to be saved in another folder/drive on the LAN) the links get patched up automatically by Excel. "Tom S" wrote: Hi everyone, In Excel, how would I go about linking one cell to another cell in a different workbook? I understand I can type = then point it to the cell in the other workbook, however my problem is everytime the location of the file changes, the links mess up. For example, I have this command to link to a file called grid: =[grid.xlsx]Sheet1!$A$1 But when I want to move both the original file and the grid file to a different location (for example on a memory stick), the link fails and tries to point back to where the file was originally stored. Is it possible to use an apsolute refrerence for the link - like I would do with cells, so it always looks for the file 'grid.xlsx' in the folder? I really need your help on this one, I'm sure it's easy enough it's just I can't figure it out myself. Thanks, Tom :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find cell in Excel2000 workbook with link from another workbook? | Excel Discussion (Misc queries) | |||
link to workbook by cell refrence | Excel Discussion (Misc queries) | |||
Link to cell in another workbook | Excel Discussion (Misc queries) | |||
link cell from one sheet to another within the same workbook | Excel Discussion (Misc queries) | |||
Link A Cell In O ne Workbook To A Formula In Another Workbook | Excel Worksheet Functions |