named ranges change after adding or deleting other named ranges

[littlepete]

hello all
i have an address list that contains all known to me people. to add their parents, i added a named range to every person that is parent of someone in my list.

but:
when i delete a row, or add one, or add a new named range, constantly (really) find other named ranges that have changed and gone wrong !
i can see that when i scroll through my list and see persons who's parents are totally wrong. i check in formula-named ranges in the list and there i can see
that that range has disappeared, or has shifted a row down or up, or sometimes to a totally different person...

i have been doing this now for four days, and of course, this way i will continue forever...

the way i add a named range is going to the person who is a parent, and at the top left where i can see the cel name (forex. "N37") i fill in the named range of that person.
the way i choose the named range is always the same: vn (first name in dutch:voornaam) + three first consonants first name + three first consonants last name...
the link in the named ranges list is always like this : " data!$N$159 "

what is it I don't see?
thank you for helping !!!
peter, belgium

 

[littlepete]

Do you only have problems on the 'gegevens' page?

hello

yes; only problems on the gegevens (=data) sheet. the other sheets do different things.

 

[BSALV]

Example
When you add an event-macro for double click on the columns C (father) or D (mother) in this example, it jumps to that same name in the B-column.
You say, you use a 8 character key, so, you can make plenty of combinations

 

[littlepete]

Example
When you add an event-macro for double click on the columns C (father) or D (mother) in this example, it jumps to that same name in the B-column.
You say, you use a 8 character key, so, you can make plenty of combinations

hello

i like that idea but what would i write in that event then? ...

 

[BSALV]

it's already there, in the "bladmodule" of that sheet, a "double-click event".
For your situation, columns C:D become AX:AY

 

[eastrand]

@littlepete, I haven't had any luck getting it to break or loose the range names when I add or delete a row. Unfortunately, that makes if difficult to diagnose when I can't see exactly what it's doing. I did see all of the #REF! errors in the name manager. How exactly are you adding rows? Can you tell me something specific that always breaks it like add a row between row 6 and 7?

 

[littlepete]

hello

first: thank you for your time !!!
i'm still not sure where the problem is. the link from parents to the child is made with a hyperlink and named ranges in it.

i made a new fake list with only belgian and dutch royalty in it.
i added parents for amalia ( maxima and willem) and for elisabeth (filip and mathilde).

when you copy for example row 14 and insert as row 15 (king willem time) you will notice
that mathilde and maxima (two rows there after) will get wrong links...
it is not clear to me why sometimes links are ruined, and sometimes not...

when handling this list you will notice another error, but you dont need to look into that...
that error came after highlighting the activecell with yellow text and white borders...
since then i need (???) to activate (?) the first sheet, i do that with control pagedown going to the next sheet
and then return to the first sheet... after which that error doesnt happen anymore...

i just want to be sure that the links from parents to child dont get ruined... i see now they are very regularly...

thank you for your research !!!
here is the link for my public list:

adressen 14 public.xlsb

Microsoft Excel Workbook

1drv.ms

 

[eastrand]

@littlepete, I cannot figure this one out yet. I'll keep working on it but unfortunately, with this being in a language I can't read, it makes it a little difficult to follow. Give me some time and I'll work on it as time permits.

 

[littlepete]

@littlepete, I cannot figure this one out yet. I'll keep working on it but unfortunately, with this being in a language I can't read, it makes it a little difficult to follow. Give me some time and I'll work on it as time permits.

I appreciate your efforts !!!

basically you should search for a (hyper)link that follows the line from a person's mom 's cell to the mom's name cell...
anyway is good... as long as i can click on a cell containing the name of one's mother and arrive at that mother's name cell
to remind you, in my case: person's name in column B. person's mother's name in Ax; father in Ay.
and each row is a person

good luck at your research !!!!

 

[littlepete]

PROBLEM SOLVED !!!

i created vba to replace the hyperlink function :
it looks in column CP of the activerow to the name of that person's mother,
then search in column B for the row containing that name !
look :

Sub vindnaam() ' ================================================= control - o

Dim zoeknaam As Range
Dim gezocht As Variant
Dim dezerij As Long
dezerij = ActiveCell.Row
gezocht = "*" & Range("cp" & dezerij).Value & "*"
Set zoeknaam = Range("rngpersoon").Find(gezocht, , xlValues, xlWhole)
'Set zoeknaam = Range("rngpersoon").Find(gezocht)

MsgBox dezerij & " - " & gezocht
If zoeknaam Is Nothing Then
Range("b" & dezerij).Select
Else
Range(zoeknaam.Address).Select
End If
End Sub

I really would like to thank all people that have given me advice, comments and help in resolving this... !!!
thank you !!!

 

[BSALV]

Problem Solved = "het warm water heruitvinden"
See #22 almost a week ago and in #24, I said replace C:D by AX:AY.
My macro is easier to call, it's an event-macro on DoubleClick.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     If Intersect(Target, Range("C:D")) Is Nothing Then Exit Sub     'doubleclick in column C or D, if not = stop
     Set c = Me.Columns("B").Find(what:=Target.Value, lookat:=xlWhole)          'zoek overeenkomstige cel in B-kolom
     If Not c Is Nothing Then                                   'gevonden ?
          Application.Goto c, 0                                 'ga er naar toe
     Else                                                       'niet gevonden
          MsgBox "foutje bedankt", vbCritical                   'boodschap
     End If
End Sub