Verwijzing naar een cel in een ander bestand

Een verwijzing naar een cel in een ander Excel bestand wordt een externe verwijzing genoemd. Zo kun je bijvoorbeeld één bronbestand bijhouden en die gegevens vervolgens op meerdere plaatsen gebruiken.

In dit artikel laat ik je eerst zien hoe je een eenvoudige externe verwijzing kunt maken. En daarna leer je hoe je het kunt oplossen mocht de koppeling niet langer werken.

Voorbeeld

Je kunt onderstaande stappen volgen om een eenvoudige externe verwijzing te maken.

1. Open een nieuw, leeg Excel bestand.
2. Vul in cel B2 een willekeurige tekst in, bijvoorbeeld Amsterdam.
3. Sla dit bestand op als Bestand 1.
4. Open een ander, leeg Excel bestand en sla dat op als Bestand 2.
5. Ga in bestand 2 vervolgens naar de cel waar je het woord uit bestand 1 wilt tonen. Bijvoorbeeld in cel A1.
6. Typ in die cel (in bestand 2 dus), de volgende formule:
   ='[Bestand 1.xlsx]Blad1'!$B$2

Wanneer je beide bestanden hebt geopend, kun je het ook op een andere manier doen. Je kunt ook het = teken typen om aan te geven dat er een formule volgt, en dan met je muis naar bestand 1 gaan en klikken op cel B2. Excel vult vervolgens de formule automatisch in:

Als het bronbestand gesloten is, toont Excel automatisch het volledige pad (bijv. C:\Gebruikers\…), maar als beide bestanden geopend zijn, zie je alleen de bestands- en bladnaam.

Sluit nu bestand 2 af en verander de waarde in bestand 1, bijvoorbeeld naar Rotterdam. Als je vervolgens bestand 2 opent, dan zie je mogelijk een waarschuwing dat het automatisch bijwerken van koppeling is uitgeschakeld. Zodra je op ‘Inhoud inschakelen’ klikt, wordt de nieuwe waarde bijgewerkt.

Wanneer je beide bestanden tegelijk hebt geopend, en de waarde in bestand 1 verandert, dan wordt de waarde in bestand 2 wel automatisch bijgewerkt.

Wanneer kan de koppeling breken?

De externe verwijzing die we zojuist hebben gemaakt bevat de bestandsnaam, het bladnaam en een celverwijzing. De koppeling kan breken als je in bestand 1:

• De naam van Bestand 1 verandert
• Het blad hernoemt of verwijdert
• Het bestand verplaatst naar een andere map (en je pad niet langer klopt)

In dat geval kan Excel een foutmelding geven. Als voorbeeld verander ik de naam van Bestand 1 in Bestand 3. De situatie is dan als volgt:

• Het bestand met in cel B2 de waarde ‘Rotterdam’ heet nu Bestand 3.
• Het bestand met de externe verwijzing heet nog steeds Bestand 2.

Wanneer ik nu Bestand 2 open, krijg ik eerst een waarschuwing te zien:

Je kunt er voor kiezen om op ‘Bijwerken’ te klikken, maar zelfs dat werkt niet omdat het pad niet langer klopt en de formule nog steeds verwijst naar ‘Bestand 1’:

Je kunt de formule aanpassen, zodat het weer verwijst naar bestand 3, en de koppeling wel weer werkt.

Gebruik een naambereik

Een andere manier om een externe verwijzing te maken is om gebruik te maken van een gedefinieerde naam. Dit voorkomt niet alle problemen. Zodra je het bestandsnaam verandert of het bestand verplaatst, dan blijft het pad niet langer werken. Maar, als je gebruik maakt van een gedefinieerde naam, is het niet langer een probleem als je het blad een andere naam geeft.

1. Open het bestand waar de waarde Rotterdam staat (of nog steeds Amsterdam als je het niet hebt verandert).
2. Ga in het lint naar Formules > Naam definiëren > Nieuw
3. Geef het een duidelijke naam, bijvoorbeeld Koppeling_Rotterdam

4. Open Bestand 2, ga naar een andere cel (bijv. cel A2), typ ='Bestand 3.xlsx'!Koppeling_Rotterdam

Het nadeel is dat je de formule helemaal handmatig moet schrijven op deze manier. Normaal gesproken zou je wellicht in het lint kiezen voor ‘Gebruiken in formule’, maar dit werkt vaak niet met een extern bestand. Gelukkig is er een klein omweggetje zodat het alsnog werkt. Zorg allereerst dat beide bestanden zijn geopend.

1. Ga naar Bestand 2 en typ =
2. Ga daarna naar Bestand 3 (de bron) en druk op F3.
3. Het venster ‘Naam plakken‘ wordt geopend. Kies de naam (in dit geval is er alleen Koppeling_Rotterdam) en klik op OK.

De formule wordt nu automatisch geschreven, zodat je alsnog de formule ='Bestand 3.xlsx'!Koppeling_Rotterdam krijgt.

Als je nu in Bestand 3 het werkblad hernoemt (Bijv. van Blad1 naar Blad3), blijft de gedefinieerde naam verwijzen naar de juiste cel. De koppeling blijft dus werken, zolang de gedefinieerde naam in Bestand 3 aanwezig blijft. Dit is niet het geval met de formule ='[Bestand 3.xlsx]Blad1'!$B$2

Wat te doen als de koppeling toch fout gaat

Wanneer je werkt met externe verwijzingen, zijn er altijd scenario’s waarbij het toch fout gaat. In dat geval zijn er een aantal dingen die je kunt doen.

1. Beide bestanden openen

Soms ververst Excel de koppelingen pas goed als beide bestanden geopend zijn. Controleer dus eerst of dat het probleem oplost.

2. Koppelingen bewerken of de bron wijzigen

Wanneer een koppeling niet meer werkt, kun je deze eenvoudig herstellen in het lint via Gegevens > Werkmapkoppelingen:

In dit venster zie je een overzicht van alle externe koppelingen die in je bestand voorkomen. In dit voorbeeld is dat met Bestand 3:

• Je kunt vervolgens op ‘Alles vernieuwen’ klikken om de gegevens opnieuw op te halen uit het bronbestand.
• Alles verbreken om de koppeling te verwijderen. De huidige waarden blijven dan staan, maar worden niet meer automatisch bijgewerkt.

Wanneer je op die ‘3 puntjes’ klikt kun je verschillende dingen instellen:

• Het bron bestand openen.
• De koppeling kopieren.
• De bron wijzigen. Hiermee kun je bladeren op je computer om het juiste bestand te selecteren als de bestandsnaam of locatie is gewijzigd. Zodra je de juiste bron selecteert, werkt de koppeling weer zoals eerst.

3. Power Query

Als je regelmatig gegevens uit andere bestanden wilt ophalen, kun je dit ook doen met Power Query. Je kunt deze optie in het lint vinden in het tabblad Gegevens > Gegevens ophalen > Uit bestand > Uit werkmap.

Deze methode is wat meer voor gevorderde Excel gebruikers, maar is een goed alternatief als je bestanden vaak van naam of locatie veranderen. Voor dit artikel wordt hier verder niet op in gegaan, omdat het anders te complex wordt, maar wellicht dat dit later in apart blogartikel nog wordt uitgewerkt.