linking between two Excel files Solved

Question

Hello,
I have an Excel file that, when opened, asks if it can update the Excel sheet with another file (which I don't have)! How do you program such a link? I've looked in the VBA code, but found nothing.
Thanks in advance to anyone who can shed some light on this!

Configuration: Windows 2000 Internet Explorer 6.0, Office Professional 2000

idclauzel · Accepted Answer

There are some sharpshooters on this forum!
I had barely written my question when I got a response, which leads to the question:
How does one make such a link? How to find it, the file with 6 sheets and quite a few cells, if I have to click on it, one by one I'll be at it all night!

pépé35530 · Answer

Hello,  you might have some cells that were copied with links in the other sheet.  See you later  grandpa

m@rina · Answer

Hello

There are several types of links.

Often, an (unwanted) link comes from copying from one workbook to another.
For example, imagine that we copy and paste a sheet from workbook A to workbook B, and that in this sheet there are formulas that refer to another sheet in workbook A...
This can come (still in the case of a copy) from inserting names of ranges that existed in the original workbook.

In current versions, you can (in principle) disable links:
Edit Menu, Links

Before, you can search in the formulas for the name of the workbook to see if it appears in the formulas and what it might cause.

Also check the names (Insert, Name, Define): if there are names that give invalid references (#REF!), they should be discarded.

m@rina

--
- "On the productivity forum, we ask questions about productivity..."
- "Oh really???"

idclauzel · Answer

Great job on the efficiency; by following your advice: Edit Menu / Link, I found the reference of two binders but now I need to find where these references are hiding! If anyone has an idea, thanks.

qmike · Answer

Hello If you are not interested in the links regarding these workbooks You can break them  1. Select the Edit menu, Links  2. Select the Break Links option

m@rina · Answer

I gave you the two clues:

1) The names: Insert, Name, Define
2) The name of the workbook in the formulas: Edit, Search (in the entire workbook)

m@rina
--
- "On the office forum, questions are asked regarding office work..."
- "Oh really??? "

pépé35530 · Answer

Maybe use the "SEARCH" command.  You can also request the display of the cell contents.  See you later  Grandpa

idclauzel · Answer

Thank you for the answers, but they must not correspond to my version (Office 2000) because in Edit/Link I don't have the "Break Links" checkbox
And in Edit/Search I don't have the "Entire Workbook" option
I tried a search, sheet by sheet (there are 5) with the "In Formulas" option for the string xls since it’s a linked Excel file, I even have its name with the "Link" window
The problem is not so much to get rid of it because it's a small test program that was given to me, but rather to understand "How it Works"!!! I might have a use for it!

m@rina · Answer

Indeed, as I mentioned, since version 2002, you can remove links via the Edit menu, Links.
For older versions, there’s a macro created by I can’t remember who that searched for and removed links, as it was a real problem.

Have you also checked the names? (Yes, I know... I insist! :d)))

m@rina
--
- "On the office forum, we ask questions about office software..."
- "Oh really???"

idclauzel · Answer

Thank you Marina

I have a problem, I know EXCEL well but in the "basic" area, however I know ACCESS very well and I'm trying out EXCEL/ACCESS and EXCEL/EXCEL links.
Hence my question about linking Excel files to Excel files. I haven't found any help. Your remark "have you checked the Names" is probably very insightful but I don't know what you mean, I found "Name" in the "Insert" dropdown menu but that can't be it!
If anyone can tell me what I need to read and where regarding this communication issue, I would be very happy!
Thanks in advance to everyone
Dominique

pépé35530 · Answer

Hello,

Creating a name means giving a name to a set of cells. Then, you can use this name in formulas without having to specify these cells.
Instead of having, for example, +A1*B1, you can write Quantity*PU (if you have named cell A1 Quantity and cell B1 PU). This obviously works for a group of cells.

a+

grandpa

eriiic · Answer

Hello,  understanding "How It Works"  Open 2 workbooks, Workbook1 and Workbook2 Select Workbook1 A1, enter =, activate Workbook2, click on A1, the formula =[Workbook2]Sheet1!$A$1 will be entered, press enter to validate. You just created a link in your Workbook1 that you can view in 'edit / links... If you enter 5 in [Workbook2]Sheet1!$A$1 you retrieve it in [Workbook1]Sheet1!$A$1  Now copy the link formula =[Workbook2.xls]Sheet1!$A$1 menu 'insert / name / define...', 'name in the workbook:' test, 'refers to:' =[Workbook2.xls]Sheet1!$A$1 OK You just defined a link in a name that you can check by the same path by selecting it from the list (it's this type that we often forget that m@rina was talking about). In another cell of Sheet1, if you enter =test, you retrieve your value 5  Now close Workbook2 and check your formula in A1, it becomes: ='D:\...\[Workbook2.xls]Sheet1'!$A$1 The path is hardcoded, if you want to move Workbook2 you need to open it and do 'save as' before deleting the original if you want the links to follow.  And sometimes you have #REF in your links and that's when it gets annoying because you have to rebuild them one by one and it's sometimes mission impossible...  eric

idclauzel · Answer

Thank you Eric

I had been looking for this information for a long time! I had encountered this problem a few months ago and had worked around it!
A second encounter annoyed me, hence my question.
Your response is clear and precise, a model to follow as often as possible.
D. Clauzel

chirazay · Answer

Hello, I have the same problem too, if possible please help me if you find a solution. Thank you in advance.

chirazay · Answer

Hello, I have the same problem, please contact me if you have a solution. Thank you idclauzel in advance.

idclauzel · Answer

I didn't find the solution! Sorry.

dlareg39700 · Answer

Can you tell me which version of Excel you have? Otherwise, in version 2007 and earlier, you need to go to Edit, Links, and there you will usually find the name of the linked file. If you can't find it, you need to go to Insert, Name, Define, and there you will find defined range names. If you need more help, let me know. Have a good day.

idclauzel · Answer

Thank you, I have Excel 2000 but this problem dates back to 2009, since then I found a "by-pass" but thank you very much

Linking between two Excel files

18 réponses

Texture issues in enshrouded

Canon mb5350 - cartridge replacement impossible

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.