Dynamische verwijzing naar werkblad of werkmap in Excel

Stel, je hebt gegevens met hetzelfde formaat op meerdere werkbladen of werkmappen en je wilt deze gegevens dynamisch ophalen in een ander blad. De INDIRECT-functie kan je hierbij snel helpen.

Dynamisch cellen verwijzen in een ander werkblad
Dynamisch cellen verwijzen in een andere werkmap

Dynamisch cellen verwijzen in een ander werkblad

Stel, je hebt vier werkbladen met verschillende kwartaalverkopen voor vier verkopers en je wilt een samenvattend werkblad maken dat de kwartaalverkopen dynamisch ophaalt op basis van de betreffende verkoper. Met onderstaande formule kun je dit eenvoudig realiseren.

Algemene formule

=INDIRECT("'"&sheet_name&"'!Cel om gegevens op te halen")

1. Zoals te zien in de onderstaande schermafbeelding, maak je eerst het samenvattende werkblad door de namen van de werkbladen apart in verschillende cellen te zetten. Selecteer vervolgens een lege cel, plak onderstaande formule erin en druk op Enter.

=INDIRECT("'"&B3&"'!C3")

Opmerkingen: In de code:

• B3 is de cel met de naam van het werkblad waarvan je gegevens wilt ophalen;
• C3 is het celadres in het specifieke werkblad waarvan je de gegevens wilt ophalen;
• Om te voorkomen dat er een foutwaarde wordt weergegeven als B5 (de cel met de werkbladnaam) of C3 (de cel waarvan je gegevens wilt ophalen) leeg is, kun je de INDIRECT-formule combineren met een IF-functie zoals hieronder:
  =IF(OF(B3="",C3=""),"",INDIRECT($B$3&"!C3"))
• Als je werkbladnamen geen spaties bevatten, kun je deze formule direct gebruiken
  =INDIRECT(B3&"!C3")

2. Sleep vervolgens de vulgreep omlaag om de formule toe te passen op andere cellen. Nu heb je alle verkopen van het eerste kwartaal uit de betreffende werkbladen opgehaald.

3. Ga verder met het ophalen van alle verkopen van de andere kwartalen zoals gewenst. Vergeet niet de celverwijzing in de formule aan te passen.

Dynamisch cellen verwijzen in een andere werkmap

In dit gedeelte wordt uitgelegd hoe je dynamisch cellen in een andere werkmap in Excel kunt verwijzen.

Algemene formule

=INDIRECT("'[" & Boeknaam & "]" & Werkbladnaam & "'!" & Celadres)

Zoals te zien in de onderstaande schermafbeelding, staan de gegevens die je wilt ophalen in kolom E van het werkblad “Total sales” in een aparte werkmap met de naam “SalesFile”. Volg onderstaande stappen om dit uit te voeren.

1. Vul eerst de informatie van de werkmap in (inclusief de naam van de werkmap, het werkblad en de te verwijzen cellen), zodat je op basis hiervan gegevens kunt ophalen in de huidige werkmap.

2. Selecteer een lege cel, plak onderstaande formule erin en druk op Enter.

=INDIRECT("'["&$B$3&"]"&$C$3&"'!"&D3)

Opmerkingen:

• B3 bevat de naam van de werkmap waaruit je gegevens wilt halen;
• C3 is de naam van het werkblad;
• D3 is de cel waarvan je gegevens wilt ophalen;
• De foutwaarde #REF! verschijnt als de verwezen werkmap gesloten is;
• Om de foutwaarde #REF! te voorkomen, kun je de INDIRECT-formule combineren met de IFERROR-functie zoals hieronder:
  =IFERROR(INDIRECT("'["&$B$3&"]"&$C$3&"'!"&D3),"")

3. Sleep vervolgens de vulgreep omlaag om de formule toe te passen op andere cellen.

Tip: Als je niet wilt dat de resultaatwaarde een foutmelding geeft nadat je de verwezen werkmap hebt gesloten, kun je de naam van de werkmap, het werkblad en het celadres direct in de formule opgeven, zoals hieronder:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,"")

Gerelateerde functie