Excel dynamisch werkblad of werkmapreferentie

Stel dat u gegevens met dezelfde indeling hebt op meerdere werkbladen of werkmappen en gegevens van deze werkbladen of werkmappen dynamisch in een ander blad moet krijgen. De INDIRECTE functie kan u helpen het snel voor elkaar te krijgen.

Verwijs dynamisch naar cellen in een ander werkblad
Verwijs dynamisch naar cellen in een andere werkmap

Verwijs dynamisch naar cellen in een ander werkblad

Stel dat er vier werkbladen zijn met verschillende verkopen in kwartalen voor vier verkopers, en u wilt een samenvattend werkblad maken om dynamisch de kwartaalverkopen op te halen op basis van de corresponderende verkoper. Om het te laten werken, kan de onderstaande formule helpen.

Generieke formule

=INDIRECT("'"&sheet_name&"'!Cell to return data from")

1. Zoals de onderstaande schermafbeelding laat zien, moet u eerst het samenvattende werkblad maken door de bladnamen afzonderlijk in verschillende cellen in te voeren, vervolgens een lege cel selecteren, de onderstaande formule erin kopiëren en op de Enter sleutel.

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

Notes: In de code:

• B3 is de cel met de bladnaam waaruit u gegevens wilt ophalen;
• C3 is het celadres in het specifieke werkblad waar u de gegevens uit haalt;
• Om te voorkomen dat de foutwaarde wordt geretourneerd als B5 (de cel met de bladnaam) of C3 (de cel waarin u de gegevens gaat ophalen) leeg is, moet u de INDIRECTE formule omsluiten met een ALS-functie die wordt weergegeven zoals hieronder:
  = ALS (OF (B3 = "", C3 = ""), "", INDIRECT ($ B $ 3 & "! C3"))
• Als er geen spaties in uw bladnamen staan, kunt u deze formule direct gebruiken
  = INDIRECT (B3 & "! C3")

2. Sleep vervolgens zijn Vul de handgreep omlaag om de formule op andere cellen toe te passen. Nu heb je alle verkopen van het eerste kwartaal uit de specifieke werkbladen geretourneerd.

3. Ga je gang om alle verkopen van andere kwartalen binnen te halen als je nodig hebt. En vergeet niet om de celverwijzing in de formule te wijzigen.

Verwijs dynamisch naar cellen in een andere werkmap

In dit gedeelte wordt gesproken over het dynamisch verwijzen naar cellen in een andere werkmap in Excel.

Generieke formule

=INDIRECT("'[" & Book name & "]" & Sheet name & "'!" & Cell address)

Zoals de onderstaande schermafbeelding laat zien, lokaliseren de gegevens die u wilt retourneren in kolom E van het werkblad "Totale verkoop" in een aparte werkmap met de naam "SalesFile". Doe als volgt stap voor stap om het voor elkaar te krijgen.

1. Laten we eerst de werkmapinformatie invullen (inclusief de werkmapnaam, werkbladnaam en referentiecellen), die u op basis van deze informatie naar de huidige werkmap gaat ophalen.

2. Selecteer een lege cel, kopieer de onderstaande formule erin en druk op Enter sleutel.

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

Notes:

• B3 bevat de werkmapnaam waaruit u de gegevens wilt extraheren;
• C3 is de bladnaam;
• D3 is de cel waaruit u gegevens gaat ophalen;
• Dé #REF! foutwaarde wordt geretourneerd als de werkmap waarnaar wordt verwezen, wordt gesloten;
• Om de #REF! foutwaarde, omsluit de INDIRECTE formule als volgt met de IFERROR-functie:
  = IFERROR (INDIRECT ("'[" & $ B $ 3 & "]" & $ C $ 3 & "'!" & D3), "")

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

Tip: Als u niet wilt dat de retourwaarde een fout wordt na het sluiten van de werkmap waarnaar wordt verwezen, kunt u de werkmapnaam, de werkbladnaam en het celadres als volgt rechtstreeks in de formule opgeven:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,"")

Gerelateerde functie