Link cell to another workbook

[Tom S]

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

 

[JLatham]

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]

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.

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.

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

 

[מיכ×ל (מיקי) ×בידן]

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

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.

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.

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

 

[מיכ×ל (מיקי) ×בידן]

After all selections push "Change All"
Micky

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

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.

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.


:

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

 

[JLatham]

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.

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.

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.

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