Indirecte verwijzing

shakespeareDirect or Indirect, that’s the question!

Heb je je dit ook al ooit afgevraagd?
Als ik deze vraag beperk tot het gebruik van Excel, dan denk ik dat het antwoord meestal ontkennend zal zijn.
Het gebruik van de functie INDIRECT is niet wijdverbreid; maar misschien is de uitdrukking Onbekend maakt onbemind wel van toepassing.

Ik kreeg vorige week een vraag van iemand van een bridgeclub, die de wekelijkse resultaten automatisch wilde laten verwerken in een totaal-sheet. Ze dacht, dat daarbij VBA/programmeren aan te pas zou moeten komen, maar hier bleek de functie INDIRECT uitkomst te bieden.

Directe en indirecte verwijzingen

Indirect1Laten we eerst eens even kijken wat dat Indirect nu precies is, voordat we in een (simpel) voorbeeld enkele mogelijkheden gaan onderzoeken.

In het Voorbeeldbestand heb ik in het tabblad Vb1a een maandoverzicht met bedragen staan (kolommen B en C).
Daarnaast staat een compacter overzicht waar maar enkele maanden zijn opgenomen (kolom E). Wanneer we de betreffende bedragen daarnaast willen hebben, dan kunnen we in cel F4 intikken =C3.
Dit wordt een verwijzing genoemd en in dit geval een directe verwijzing: we nemen direct de waarde uit C3 over in cel F4.
In cel G4 zie je hetzelfde resultaat, maar daar staat als formule een indirecte verwijzing
=INDIRECT(“C3”)

Behalve, dat dit nou geen echt zinvol gebruik van deze functie is, kan ik daarmee wel de kracht van de functie uitleggen: Indirect vertaalt inwendig een tekst (let op de aanhalingstekens!) naar een directe celverwijzing.
Uiteraard moet die tekst dan wel een geldige verwijzing voorstellen (dus bijvoorbeeld geen spatie tussen de kolomletter en het rijnummer).

Maar de kracht van deze vertaalmogelijkheid komt pas tot uiting in cel G5. Hier moet het bedrag (in kolom C) van de 3e maand (cel E5) opgehaald worden:
=INDIRECT(“C”&E5)

Door het &-teken worden twee teksten gekoppeld: de letter C en de inhoud van cel E5 (zie het artikel Teksten samenvoegen).

Ai, jammer: dit klopt niet helemaal. Op deze manier wordt een verwijzing naar cel C3 gemaakt; maar maand 3 staat in rij 5, maand 4 in 6 etc. Dus altijd 2 lager dan het maandnummer aangeeft.
Oplossing:
=INDIRECT(“C”&(E5+2))
De letter C wordt aan de inhoud van cel E5 plus 2 gekoppeld; in dit geval levert dat als verwijzing C5 op.

NB de haakjes rond de optelling staan er alleen voor de duidelijkheid; zonder haakjes zou Excel hetzelfde resultaat opleveren.

De formule in cel G5 kan nu naar beneden gekopieerd worden naar G6 t/m G8 en in die cellen verschijnt het bedrag van de betreffende maanden.
Wanneer in de cellen E5 t/m E8 andere maandnummers worden ingevoerd, zullen de overeenkomende bedragen direct in kolom G getoond worden.
Het overzicht is daardoor flexibeler/dynamischer geworden, vergeleken met directe verwijzingen.

NB overal waar je in Excel een directe verwijzing zet (of dit in een formule direct achter het =-teken is, als parameter in een formule, in een rekenkundige bewerking) kan ook een indirecte verwijzing worden geplaatst.

Verwijzingstype

Indirect2In het tabblad Vb2 van het Voorbeeldbestand staat een zelfde soort maandoverzicht, maar nu ‘horizontaal’.

Nu wordt het opzoeken van het bedrag, dat bij de 3e maand (cel B8) hoort, wat lastiger:
alle bedragen staan in rij 3; dus het laatste gedeelte van de verwijzing is altijd gelijk aan 3. Nu de juiste kolom vinden: de 3e maand staat in de 5e kolom etc.; dus ook hier moeten we 2 optellen bij het maandnummer. Maar dat is niet voldoende: het kolomnummer moet omgezet worden naar een kolomletter. In Excel kan dat met de functie TEKEN.
Als we dan ook nog weten, dat de code 65 de letter A voorstelt, 66 de B etc. dan zou de formule in cel D8 te doorgronden moeten zijn:
=INDIRECT(TEKEN(64+B8+2)&”3″)

Het omzetten van een kolomnummer naar een letter is echter niet persé nodig: de functie Indirect kent nog een optionele parameter (die mag maar hoeft dus niet ingevuld te worden). Via deze parameter kunnen we aangeven, dat de verwijzing al dan niet de vorm RxKy (rij x, kolom y) heeft.
Het bedrag van de 3e maand (B8 bevat de waarde 3) ophalen kan dan ook als volgt:
=INDIRECT(“R3K”&(B8+2);ONWAAR)

De parameter ONWAAR geeft aan, dat het verwijzingstype de vorm RxKy heeft; wanneer als parameter WAAR wordt meegegeven (of wanneer de parameter wordt weggelaten zoals hiervoor) dan heeft de verwijzing de standaardvorm A1 (kolomletter, gevolgd door rijnummer).

Verwijzing naar een andere werkmap of werkblad

Vaak worden samenvattingsoverzichten op een ander werkblad (of in een andere werkmap) geplaatst; niet bij de basisgegevens zelf.

Indirect3In het Voorbeeldbestand, tabblad VB3, willen we de gegevens van het tabblad Vb1a overnemen.
In cel C3 staat een directe verwijzing naar cel C3 van het tabblad Vb1a; de naam van het tabblad (Vb1a) wordt gescheiden van de celnaam door een uitroepteken.
De indirecte verwijzing in cel D4 wordt daarom:
=INDIRECT(“Vb1a!C”&(B4+2))

En ook deze formule kan nu zonder verdere aanpassingen naar beneden gekopieerd worden.

LET OP wanneer de naam van een tabblad uit meerdere woorden bestaat, gescheiden door een spatie of een ander leesteken, niet zijnde een underscore (_), dan moeten rond deze naam rechte, enkele aanhalingstekens (‘) geplaatst worden.

Wanneer de brongegevens in een andere werkmap staan, maak dan eerst een directe verwijzing naar een cel in dat bronbestand (door in een cel het =-teken te plaatsen en dan met de muis ergens in het bronbestand te klikken). Binnen de functie Indirect zal dan eenzelfde verwijzing als tekst moeten worden opgebouwd.

Nog een voorbeeld

In het Voorbeeldbestand is een tabblad Vb1b opgenomen, dat qua opzet gelijk is aan het tabblad Vb1a.

Indirect4Het resultaat in de kolommen G en H van het tabblad Vb3 is via de functie Indirect tot stand gekomen.
Bijvoorbeeld in cel G3 staat de formule:
=INDIRECT(“‘”&G$2&”‘!C”&($F3+2))

Let op de plaats van de enkele aanhalingstekens en de gedeeltelijk absolute en gedeeltelijk relatieve verwijzingen naar G$2 en $F3.

De formule in G3 is naar rechts en naar beneden gekopieerd.


8 gedachten over “Indirecte verwijzing

  1. Ik wil graag een dynamische verwijzing maken naar een andere Excel die gesloten is op een netwerkschijf.

    Momenteel staat in kolom B telkens een verwijzing die een waarde op haalt:
    Voorbeeld:
    B1: =’G:\TEAM-Rapporten\OVL\[20180903.xlsm]OVERVIEW’!A15
    B2: =’G:\TEAM-Rapporten\OVL\[20180904.xlsm]OVERVIEW’!A15
    B3: = ‘G:\TEAM-Rapporten\OVL\[20180903.xlsm]OVERVIEW’!A15

    Nu wil ik dat dynamisch maken maar snap niet hoe.
    In kolom A staat telkens de omgekeerde datum die ook in de file-naam staat.

    Ik wil dus zoiets ongeveer krijgen:
    =’G:\TEAM-Rapporten\OVL\[A1.xlsm]OVERVIEW’!A15
    =’G:\TEAM-Rapporten\OVL\[A2.xlsm]OVERVIEW’!A15

    Met de functie “indirect” lukt dat niet (alle excel files moeten dan open staan).
    Kan er hier iemand mee helpen want ik blijf maar zoeken.

    1. Dag Patrick,
      zoals je hieronder bij de andere reacties kunt zien, lukt het niet met INDIRECT.
      En nog erger: ik heb ook nog geen andere oplossing!
      Misschien iemand anders?

      Mvg

      Gijs Verbruggen

  2. hallo aan allen,

    is er een mogelijkheid een andere waarde te gebruiken in een formule welke staat ingevuld in een bepaalde cel .zie voorbeeld hieronder:
    als je een bepaalde lijst hebt gemaakt van de te selecteren namen , je klikt op formules, namen beheren, dan zie je onder de tap namen vb a1 staan(dit kan je een andere omschrijving geven vb pers1) en onder tap waarde zie je de gesecteerde naam staan .
    hoe kan je dan pers1 (a1) in een formule benaderen om dan alsdusdanig in een formule te gebruiken
    thanks

    1. Beste Dominic,

      in de Engelstalige versies moet je CHAR gebruiken ipv TEKEN.

      Bij al mijn artikelen horen voorbeeldbestanden.
      Wanneer je die downloadt zie je de functiebenamingen zoals die horen bij de Excel-versie die u gebruikt, dus niet per se Nederlands.

  3. Goedemiddag Gijs,

    Helaas werkt de indirect formule niet bij afgesloten bestanden. Eerder werkt ik daarom met de indirect.ext formule maar die werkt niet meer onder excel 2016. Zijn hier nieuwe oplossingen voor gekomen.

    Ik ben benieuwd.

    Hoop op een positieve reactie.

    Met vriendelijke groet,
    Wim van der Linden

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *