onrechtstreekse verwijzing: waarde cel "ouders" plaatsen in rij "kind"

[littlepete]

hallo
mijn adressenlijst werkt perfect...
basically is het een adressenlijst met alle mogelijke gegevens van elke persoon.
rijen 1-3 zijn koptitels; rij 4 is de autofilter, en vanaf rij 5 gegevens.
de kolommen a-f zijn zichtbaar op het scherm, en geven in mooi nederlands de gegevens weer : niet: "geboortedatum: 19.04.1961" maar "x werd geboren op woensdag 19 april 1961 in Rocourt"...
kolom D bevat een lijn met "moeder: " en "vader: "
kolom k-by bevatten alle gegevens.
kolom N (voornaam) ; kolom O (naam) ; kolom Q (transcript) ; kolom BR (moeder) en kolom BS (vader)

probleem:
alle namen worden geschreven in de eigen taal, er zitten een aantal arabische namen in, en in transcriptie geef ik de naam weer in onze letters.
deze twee kolommen vul ik zelf in, door de naam van die persoon te kopieren vanuit diens eigen rij.

hoe kan ik in de rij van zoon of dochter (bv. in rij 10), in de kolom D na "moeder: " of "vader: " verwijzen naar de cel met de naam van moeder (rij 68) of vader (rij103)
in plaats van deze over te tikken?

hartelijk dank voor de hulp !
peter, zoon van jeroom en hilde

 

[GWteB]

als ik in de toekomst een persoon toevoeg schuiven alle volgende rijen (en dus de verwijsnummers) OOK op ! en dan
zou ik dus alle rijen met ouders opnieuw moeten nakijken...

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

=INDIRECT( "N" & BZ5 )

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 ik elke cel van een moeder en vader een range naam geef en dan in die formule indirect("N"&naamvader) zet
in plaats van indirect("N"&bz5) : blijft dan de verwijzing doorgaan naar de juiste cel ? of moet er dan nog wat gebeuren?

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
Sheet1

Cell Formulas
Range	Formula
C4	C4	=INDIRECT("R" & F4 & "K" & E4; ONWAAR) & " " & INDIRECT("R" & H4 & "K" & G4; ONWAAR)
D4	D4	=INDIRECT("R" & J4 & "K" & I4; ONWAAR) & " " & INDIRECT("R" & L4 & "K" & K4; ONWAAR)
E4	E4	=KOLOM(A:A)
F4,H4	F4	=RIJ(8:8)
G4	G4	=KOLOM(B:B)
I4	I4	=KOLOM(A:A)
J4,L4	J4	=RIJ(6:6)
K4	K4	=KOLOM(B:B)

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).

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
Sheet2

Cell Formulas
Range	Formula
C4	C4	=INDIRECT(E4 & F4) & " " & INDIRECT(G4 & H4)
D4	D4	=INDIRECT(I4 & J4) & " " & INDIRECT(K4 & L4)
E4	E4	=KOLOMLETTER(A:A)
F4,H4	F4	=RIJ(8:8)
G4	G4	=KOLOMLETTER(B:B)
I4	I4	=KOLOMLETTER(A:A)
J4,L4	J4	=RIJ(6:6)
K4	K4	=KOLOMLETTER(B:B)

 

[littlepete]

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

=INDIRECT( "N" & BZ5 )

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
Sheet1

Cell Formulas
Range	Formula
C4	C4	=INDIRECT("R" & F4 & "K" & E4; ONWAAR) & " " & INDIRECT("R" & H4 & "K" & G4; ONWAAR)
D4	D4	=INDIRECT("R" & J4 & "K" & I4; ONWAAR) & " " & INDIRECT("R" & L4 & "K" & K4; ONWAAR)
E4	E4	=KOLOM(A:A)
F4,H4	F4	=RIJ(8:8)
G4	G4	=KOLOM(B:B)
I4	I4	=KOLOM(A:A)
J4,L4	J4	=RIJ(6:6)
K4	K4	=KOLOM(B:B)

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).

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
Sheet2

Cell Formulas
Range	Formula
C4	C4	=INDIRECT(E4 & F4) & " " & INDIRECT(G4 & H4)
D4	D4	=INDIRECT(I4 & J4) & " " & INDIRECT(K4 & L4)
E4	E4	=KOLOMLETTER(A:A)
F4,H4	F4	=RIJ(8:8)
G4	G4	=KOLOMLETTER(B:B)
I4	I4	=KOLOMLETTER(A:A)
J4,L4	J4	=RIJ(6:6)
K4	K4	=KOLOMLETTER(B:B)

wow !!! zal dat nog een paar keer lezen om het helemaal te bevatten !!!

ondertussen ben ik wél begonnen aan het toevoegen van een named range aan elke voornaam van iemand die ouder is.
maar, ik merk om de haverklap dat sommige named ranges van plaats veranderen, m.a.w. na een tijdje als ik er weer enkele heb toegevoegd,
kijk ik naar de namen van de ouders en merk dat ze verkeerde namen hebben.

ik maak de named ranges door links bovenaan waar de verwijzing naar de cel staat te klikken en daar tik ik de naam van de range.
is dat een goeie manier? of moet ik via formules - namen in de lijst van de named ranges daar toevoegen kiezen en zo toevoegen?
het is eigenlijk wel frustrerend dat er altijd enkele zijn die toch de verkeerde namen tonen ondanks dat ik wel echt de juiste ouders een
named range geef...

tot slot nogmaals dank voor alle uitleg vind het super boeiend !!!

 

[GWteB]

ik maak de named ranges door links bovenaan waar de verwijzing naar de cel staat te klikken en daar tik ik de naam van de range.
is dat een goeie manier?

Ja, dat kan prima. Wel bijzonder dat er toch verkeerde namen opduiken.
Zoals ik in mijn vorige post heb uitgelegd, past Excel formules met "zachte" verwijzingen automatisch aan bij invoegen of verwijderen van kolommen of rijen. Gelukkig ook maar want anders zouden we handen vol werk hebben. Deze automatische aanpassing geldt ook voor "named ranges", het onderliggende adres (bijv: $G$23) verandert dan automatisch mee, zodat toch de juiste waarde wordt opgehaald. Ik denk dan ook dat er bij u iets anders aan de hand is.

Graag gedaan en succes met uw project!

 

[smozgur]

@littlepete - The post answered the question should be marked as the solution, unless you post your own solution. As far as I can follow the thread (by also using Google Translator help), and as you also confirmed in your feedback, #11 is the solution post. Therefore, I switched the solution post accordingly.