named ranges veranderen van cel ongevraagd en ongemerkt !

[littlepete]

kan iemand mij alstublieft dringend als het kan helpen met een zéér merkwaardig probleem:

in mijn adressenlijst heb ik een kolom moeder en vader die zijn ingevuld bij elke persoon wiens ouders in de lijst voorkomen.
eerst had ik de namen gewoon overgetikt. nu ben ik echter alle voornamen van iemand die ouder is van iemand in de lijst
aan het vervangen door een named range: peter dejaeger wordt dan: vnptrdjg en in de cel vader van zijn kinderen staat dan " = vnptrdjg "

helaas...
naarmate ik verderwerk (vandaag de vierde dag) merk ik om de haverklap dat mensen wiens ouders reeds zijn ingevuld door named ranges,
die namen VERKEERD ZIJN !!! de named range die ik dan in de lijst opzoek staat er nog maar met een verkeerde verwijzing, dikwijls één of twee
rijen hoger of lager !!!

HOE KAN DAT ? ik word hier echt gek van, zo blijf ik bezig met voornamen vervangen door named ranges die er al stonden...
het aanmaken van een named range doe ik door links bovenaan waar ik de celverwijzing zie te klikken en daar de naam van de named range te tikken.

hoe los ik dit op???

hartelijk dank als iemand weet hoe dit op te lossen !!!
peter

 

[GraH]

Hallo Peter,

Merkwaardige manier om met named ranges te werken. Het is me een raadsel wat je daarmee wenst te bereiken? Named ranges zijn vooral bedoeld om zoals met tabellen met gestructureerde referenties te werken.

Ik kan niet onmiddellijk begrijpen wat hier gaande is. Ik kom niet verder dan een PBCAK (problem between chair and keyboard , #grapje hoor). Daar help ik je uiteraard niet mee. Ik bedoel ermee dat ik vermoed dat je het concept van een named range misschien verkeerd verstaat.

Stel dat Peter in cel A7 staat. Je noemt die cel "Peter". Er komen rijen met gegevens bij. Cell A7 zal nog steeds "Peter" heten. Maar de inhoud van A7 is misschien Sonja geworden.

Zou dat kunnen spelen?

 

[littlepete]

Hallo Peter,

Merkwaardige manier om met named ranges te werken. Het is me een raadsel wat je daarmee wenst te bereiken? Named ranges zijn vooral bedoeld om zoals met tabellen met gestructureerde referenties te werken.

Ik kan niet onmiddellijk begrijpen wat hier gaande is. Ik kom niet verder dan een PBCAK (problem between chair and keyboard , #grapje hoor). Daar help ik je uiteraard niet mee. Ik bedoel ermee dat ik vermoed dat je het concept van een named range misschien verkeerd verstaat.

Stel dat Peter in cel A7 staat. Je noemt die cel "Peter". Er komen rijen met gegevens bij. Cell A7 zal nog steeds "Peter" heten. Maar de inhoud van A7 is misschien Sonja geworden.

Zou dat kunnen spelen?

hallo,

er zijn op dit moment drie verschillende manieren om de ouders van een persoon weer te geven:
1. de eerste keuze was gewoon de naam zelf te tikken, voor- en achternaam...
2. daarna probeerde in indirect met de formule waarvan ik dacht dat deze zich zou aanpassen als de rijen verschoven... maar helaas,
ze verschoven eigenlijk niet maar de namen veranderden dus wel
3. ook opgegeven, en dan maar named ranges geprobeerd omdat ik dacht te weten dat deze niet van plaats zouden veranderen...
maar ook dat klopte blijkbaar niet... de namen van de ranges bleven maar de verwijzing ging naar andere namen zo merkwaardig...

ik blijf dus hopen op een oplossing met de indirect functie...

 

[GraH]

Kan je wat voorbeeld, zij het fake, data delen? Via XL2BB is dat makkelijk.
Je beschrijving is me net iets te cryptisch. Ik "zie" dus niet wat je wenst te bereiken. Maar ik denk wel dat het kan mits toepassing van de juiste techniek.

 

[littlepete]

Kan je wat voorbeeld, zij het fake, data delen? Via XL2BB is dat makkelijk.
Je beschrijving is me net iets te cryptisch. Ik "zie" dus niet wat je wenst te bereiken. Maar ik denk wel dat het kan mits toepassing van de juiste techniek.

adressenlijst 29 mrexcel.xlsm

Microsoft Excel Workbook

1drv.ms

dit is een link naar mijn cloud waar het bestand staat succes !!!

 

[GraH]

Nu heb je wel persoonlijke gegevens gewoon publiek gezet. Vandaar de vraag om data te anoniemiseren.

 

[littlepete]

Nu heb je wel persoonlijke gegevens gewoon publiek gezet. Vandaar de vraag om data te anoniemiseren.

geen zorgen ik heb alle namen (behalve de mijne) veranderd allemaal fake lol (ik had dit bestand al naar iemand anders ook gestuurd, vandaar het nu snelle doorsturen )

 

[GraH]

Allemaal fake... Ik hoop dat dat ook geldt voor de telefoonnummers, en zo?

Goed, dus... Denk dat ik het probleem begrijp nu.
De namen van de kolom [N] wens je te gebruiken in de kolommen [BR] en [BS], maar dan wel voor andere lijnen.

Probleem is dus dat deze werktabel tevens je referentie tabel is. Ik zie dan ook geen ander manier dan dat je de naam manueel dient in te geven. Je kan hem niet opzoeken of ophalen of zo. Named ranges zijn hier ook geen oplossing, daar ze verwijzen naar een celadres en dus niet naar de inhoud.

Geduld, misschien dat anderen hier wel een oplossing zien.

 

[GWteB]

@littlepete, heb even naar je Excel werkmap gekeken en zie nu waar de spreekwoordelijke schoen wringt. De gegevens van vader en moeder worden nl. getrapt opvraagt, althans, dat wordt gepoogd, hetgeen dus tot de door u genoemde teleurstellingen leidt. Hoe dan ook, 'k heb de vrijheid genomen in lichte mate aan uw bestand te sleutelen waarbij ik uitvoering heb gegeven aan hetgeen ik in onderstaande post heb geprobeerd uit te leggen.

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

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

www.mrexcel.com

Ik zal opsommen wat ik heb gedaan.
- allereerst heb ik in de VBE een nieuwe code module toegevoegd en de UDF uit voorgaande post daarin opgenomen, te weten KOLOMLETTER().
- op rij 7 zag ik een tekst betreffende een vader en moeder in kolom F; het betrof hier de persoon met de naam Alani Sagovitsj; met die rij ben ik aan de slag gegaan.
- in de (zeer uitgebreide) formule in F7 zag ik, dat onderstaand formuledeel voor deze tekst verantwoordelijk was:

&IF(AND(BR7<>"";BS7<>"");"moeder & vader zijn "&PROPER(INDIRECT("N"&$BR7))&" en "&PROPER(INDIRECT("N"&$BS7))&". ";"")

- op rij 7 in kolom BR stond het getal 147 en in kolom BS stond het getal 174;
- inmiddels begreep ik dat deze getallen de rij nummers betroffen van achtereenvolgens moeder en vader en dat N de gewenste kolom betrof met hun namen;
- ik heb deze "harde" nummers "zacht" gemaakt door er een formule van te maken met de werkbladfunctie RIJ();
- ik heb elke formule vervolgens uitgebreid door er de kolomletter aan vooraf te laten gaan met de tailor-made werkbladfunctie KOLOMLETTER();
- de resulterende formule in cel BR7 zou dan zijn:

=KOLOMLETTER(N:N)&ROW(147:147)

- deze formule zou evalueren naar de tekst N147 (het cel adres van de voornaam van moeder);
- als deze formule zou worden gewikkeld in de INDIRECT() functie, dan zou de resulterende formule evalueren naar de voornaam van moeder;
- omdat de achternaam (in naburige kolom O) waarschijnlijk ook gewenst is, ziet de uiteindelijke formule in cel BR7 er als volgt uit:

=INDIRECT(kolomletter(N:N)&ROW(147:147)) & " " & INDIRECT(kolomletter(O:O)&ROW(147:147))

- deze formule haalt dus de voornaam uit cel N147 gevolgd door een spatie en de achternaam uit cel O147;
- voor vader geldt een vergelijkbare formule in cel BS7;
- het formuledeel in cel F7 heb ik aangepast door de INDIRECT() functie in zijn geheel te verwijderen en direct te verwijzen naar de adressen BR7 en BS7;
- het formuledeel komt er dan zo uit te zien:

&IF(AND(BR7<>"";BS7<>"");"moeder & vader zijn "&PROPER($BR7)&" en "&PROPER($BS7)&". ";"")

- tot slot heb ik ter controle op (beide lege) rijen 147 en 174 even een naam van een vader en moeder verzonnen en ingevuld.

Omdat door deze wijzigingen de kolom letters en rij nummers van vader en moeder thans referenties zijn, zullen zij altijd mee veranderen als er rijen en of kolommen worden ingevoegd of verwijderd.
Overtuig jezelf daarvan in het gewijzigde bestand, waarin ik na toepassing van alles hierboven opgesomd, een paar rood gearceerde rijen en kolommen heb ingevoegd.

download via WeTransfer

 

[littlepete]

@littlepete, heb even naar je Excel werkmap gekeken en zie nu waar de spreekwoordelijke schoen wringt. De gegevens van vader en moeder worden nl. getrapt opvraagt, althans, dat wordt gepoogd, hetgeen dus tot de door u genoemde teleurstellingen leidt. Hoe dan ook, 'k heb de vrijheid genomen in lichte mate aan uw bestand te sleutelen waarbij ik uitvoering heb gegeven aan hetgeen ik in onderstaande post heb geprobeerd uit te leggen.

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

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

www.mrexcel.com

Ik zal opsommen wat ik heb gedaan.
- allereerst heb ik in de VBE een nieuwe code module toegevoegd en de UDF uit voorgaande post daarin opgenomen, te weten KOLOMLETTER().
- op rij 7 zag ik een tekst betreffende een vader en moeder in kolom F; het betrof hier de persoon met de naam Alani Sagovitsj; met die rij ben ik aan de slag gegaan.
- in de (zeer uitgebreide) formule in F7 zag ik, dat onderstaand formuledeel voor deze tekst verantwoordelijk was:

&IF(AND(BR7<>"";BS7<>"");"moeder & vader zijn "&PROPER(INDIRECT("N"&$BR7))&" en "&PROPER(INDIRECT("N"&$BS7))&". ";"")

- op rij 7 in kolom BR stond het getal 147 en in kolom BS stond het getal 174;
- inmiddels begreep ik dat deze getallen de rij nummers betroffen van achtereenvolgens moeder en vader en dat N de gewenste kolom betrof met hun namen;
- ik heb deze "harde" nummers "zacht" gemaakt door er een formule van te maken met de werkbladfunctie RIJ();
- ik heb elke formule vervolgens uitgebreid door er de kolomletter aan vooraf te laten gaan met de tailor-made werkbladfunctie KOLOMLETTER();
- de resulterende formule in cel BR7 zou dan zijn:

=KOLOMLETTER(N:N)&ROW(147:147)

- deze formule zou evalueren naar de tekst N147 (het cel adres van de voornaam van moeder);
- als deze formule zou worden gewikkeld in de INDIRECT() functie, dan zou de resulterende formule evalueren naar de voornaam van moeder;
- omdat de achternaam (in naburige kolom O) waarschijnlijk ook gewenst is, ziet de uiteindelijke formule in cel BR7 er als volgt uit:

=INDIRECT(kolomletter(N:N)&ROW(147:147)) & " " & INDIRECT(kolomletter(O:O)&ROW(147:147))

- deze formule haalt dus de voornaam uit cel N147 gevolgd door een spatie en de achternaam uit cel O147;
- voor vader geldt een vergelijkbare formule in cel BS7;
- het formuledeel in cel F7 heb ik aangepast door de INDIRECT() functie in zijn geheel te verwijderen en direct te verwijzen naar de adressen BR7 en BS7;
- het formuledeel komt er dan zo uit te zien:

&IF(AND(BR7<>"";BS7<>"");"moeder & vader zijn "&PROPER($BR7)&" en "&PROPER($BS7)&". ";"")

- tot slot heb ik ter controle op (beide lege) rijen 147 en 174 even een naam van een vader en moeder verzonnen en ingevuld.

Omdat door deze wijzigingen de kolom letters en rij nummers van vader en moeder thans referenties zijn, zullen zij altijd mee veranderen als er rijen en of kolommen worden ingevoegd of verwijderd.
Overtuig jezelf daarvan in het gewijzigde bestand, waarin ik na toepassing van alles hierboven opgesomd, een paar rood gearceerde rijen en kolommen heb ingevoegd.

download via WeTransfer

hallo en : amai !

dat werkt, en dat zou ik dus nooit bedacht kunnen hebben, ik begin zometeen aan het invullen van de juiste verwijsnummers (met formule) in de twee kolommen ! ik begrijp dus dat met named ranges én de functie indirect geen van beiden hadden kunnen werken... hopelijk was het uitdokteren van de oplossing geen werk van uren, maar bottom line:

de oplossing werkt !!!

van harte bedankt dus, ik zou zeggen verwijder alle data en maak van deze lijst je eigen familielijst !!! bedankt !!!
peter, leuven