Ja, dat is een juiste constatering, en dat geldt overigens ook in het geval er eventueel ergens een kolom wordt ingevoegd.
In uw casus is de formule
namelijk vrij "hard" gecodeerd. Daar bedoel ik mee, dat de formule al snel niet meer klopt als er kolommen of rijen worden ingevoegd of juist verwijderd.
De letter "N" is "keihard", dus als er links van kolom N eventueel een kolom zou worden ingevoegd of worden verwijderd, dan klopt de formule niet meer. In voorgaand geval zou de BZ5 wel automatisch veranderen in CA5 (of BY5), maar .... in die cel zelf staat weer een "harde" waarde (een rijnummer), die inderdaad niet mee verandert als er waar dan ook een rij wordt ingevoegd of verwijderd.
Als u met een benoemd bereik zou werken zou dit uw probleem in één keer oplossen en zou u de INDIRECT() functie niet eens nodig hebben (u geeft dan uiteraard niet de hele rij een naam maar meteen de juiste cel, toch?), maar dat vergt dan wél, dat u élke vader en élke moeder een eigen benoemd bereik geeft, en da's best een klusje, want uiteraard moet elk benoemd bereik een unieke naam krijgen.
Ik denk dan ook, dat de benadering met gebruik van de INDIRECT() functie in uw situatie de beste is. Ik zou u willen voorstellen om niet alleen een kolom te gebruiken waarin u het
rij nummer van vader en/of moeder opneemt, maar ook een kolom waarin u het
kolom nummer van vader en/of moeder opneemt. De nummers van die rijen en kolommen maken we vervolgens "zacht" met achtereenvolgens de werkblad functies RIJ() en KOLOM(), waarna u met gebruik van de INDIRECT() functie redelijk flexibel bent. U kunt daarna namelijk naar wens kolommen en rijen verwijderen of invoegen, zonder dat u plots een andere vader of moeder krijgt ...
Hieronder een voorbeeld op kleine schaal, die u voor wat betreft de juiste rijen en kolommen vrij eenvoudig zult kunnen vertalen naar uw eigen situatie.
| Book1 |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L |
|---|
| 1 | | | | | moeder | moeder | moeder | moeder | vader | vader | vader | vader |
|---|
| 2 | | | moeder | vader | voornaam
kolom | voornaam
rij | achternaam
kolom | achternaam
rij | voornaam
kolom | voornaam
rij | achternaam
kolom | achternaam
rij |
|---|
| 3 | | | | | | | | | | | | |
|---|
| 4 | Peter | Dejaeger | Hilde Van Hulle | Jeroom Dejaeger | 1 | 8 | 2 | 8 | 1 | 6 | 2 | 6 |
|---|
| 5 | | | | | | | | | | | | |
|---|
| 6 | Jeroom | Dejaeger | | | | | | | | | | |
|---|
| 7 | | | | | | | | | | | | |
|---|
| 8 | Hilde | Van Hulle | | | | | | | | | | |
|---|
|
|---|
Zoals u ziet wijkt de formule met de INDIRECT() functie iets af van hoe u de functie hiervoor heeft gebruikt. Dit komt omdat de werkblad functie KOLOM() een nummer retourneert in plaats van een letter. In verband hiermee is het noodzakelijk, dat we aan de INDIRECT() functie laten weten, dat de functie een verwijzing krijgt aangeboden van het type R1K1 (rij komt vóór kolom & zowel rijen als kolommen zijn genummerd), in plaats van het (door de meeste Excel gebruikers gebruikte) verwijzingstype $A$1 (rij komt ná kolom & kolom naam bestaat uit één of meerdere letters).
Zelf ben ik niet zo vertrouwd met het R1K1 verwijzingstype, men zou er snel van in de war kunnen raken als men het niet gewend is. Ik maak graag gebruik van een zogenoemde UDF - User Defined (worksheet) Function - als ik de INDIRECT() functie nodig heb. Ik kan het R1K1 type dan laten voor wat het is. Daar waar de KOLOM() functie een kolom
nummer retourneert, levert onderstaande UDF namelijk een (of meerdere) kolom
letter(s).
VBA Code:
Public Function KOLOMLETTER(Optional argRng As Range) As String
If argRng Is Nothing Then Set argRng = Application.Caller
KOLOMLETTER = Left(argRng.Address(0, 0), IIf(argRng.Column > 26, IIf(argRng.Column > 702, 3, 2), 1))
End Function
Als u deze VBA functie zou willen gebruiken dient u die in een code module te plaatsen. Open daartoe de VBE met de toets combinatie ALT F11. Op het menu balkje klikt u vervolgens op Invoegen, en op de popup kiest u vervolgens
Module. Er zal zich een leeg venster openen, waarin u bovenstaande code dient te plakken. Sluit vervolgens met de toets combinatie ALT F4 de VBE weer. Terug in Excel dient u de werkmap aangepast op te slaan. Op de ribbon kiest u Bestand > Opslaan als > navigeer naar een map > klik op de
Opslaan als dropdown en klik op "Excel-werkmap met macro's (*.xlsm)" gevolgd door de Opslaan button. Vanaf dit moment kan de UDF gebruikt worden, uiteraard uitsluitend op de werkbladen van het Excel bestand waarin deze VBA code voorkomt. Zou u in een ander Excel bestand ook de KOLOMLETTER() functie willen gebruiken, dan dient u de VBA code te kopiëren en op te nemen in dat andere bestand zoals hiervoor omschreven.
Hieronder een voorbeeld met gebruikmaking van bovenstaande UDF.
| Book1 |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L |
|---|
| 1 | | | | | moeder | moeder | moeder | moeder | vader | vader | vader | vader |
|---|
| 2 | | | moeder | vader | voornaam
kolom | voornaam
rij | achternaam
kolom | achternaam
rij | voornaam
kolom | voornaam
rij | achternaam
kolom | achternaam
rij |
|---|
| 3 | | | | | | | | | | | | |
|---|
| 4 | Peter | Dejaeger | Hilde Van Hulle | Jeroom Dejaeger | A | 8 | B | 8 | A | 6 | B | 6 |
|---|
| 5 | | | | | | | | | | | | |
|---|
| 6 | Jeroom | Dejaeger | | | | | | | | | | |
|---|
| 7 | | | | | | | | | | | | |
|---|
| 8 | Hilde | Van Hulle | | | | | | | | | | |
|---|
|
|---|
