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

 

[Akuini]

Try using INDIRECT, e.g:

=INDIRECT("Sheet3!$A$3:$A$7")

 

[littlepete]

Try using INDIRECT, e.g:

=INDIRECT("Sheet3!$A$3:$A$7")

hello
this is exactly what i tried before trying the named ranges. but using indirect this happened:
each time a person was added to my address list all indirect addresses to mothers and fathers of persons in a row below the added row
became wrong, because they remained fixed at their row, but should go lower one row after each row added...
that's why i thought a named range does not change places ...
but very weirdly they do change !

 

[Akuini]

Sorry, I don't understand what you're describing.
Could you explain with a clear example, step by step what you're doing.
What I understand is this:
Say we have 2 name ranges:
"xx" > refer to: =Sheet1!$A$9
"yy' > refer to: =INDIRECT("Sheet1!$A$9")

if we delete row 6 then :
"xx" now will refer to: =Sheet1!$A$8
"yy" remains the same

so what do you expect?

 

[littlepete]

Sorry, I don't understand what you're describing.
Could you explain with a clear example, step by step what you're doing.
What I understand is this:
Say we have 2 name ranges:
"xx" > refer to: =Sheet1!$A$9
"yy' > refer to: =INDIRECT("Sheet1!$A$9")

if we delete row 6 then :
"xx" now will refer to: =Sheet1!$A$8
"yy" remains the same

so what do you expect?

ok!
thank you for explaining !!!
i tried indirect before starting with the named ranges because i saw the references went wrong after adding rows...
but, i'll try it again and see what happens !!! thank you so far !

 

[littlepete]

Sorry, I don't understand what you're describing.
Could you explain with a clear example, step by step what you're doing.
What I understand is this:
Say we have 2 name ranges:
"xx" > refer to: =Sheet1!$A$9
"yy' > refer to: =INDIRECT("Sheet1!$A$9")

if we delete row 6 then :
"xx" now will refer to: =Sheet1!$A$8
"yy" remains the same

so what do you expect?

hello,
as i promised i replaced the named ranges in my column br and bs with the number of the rows of mom and dad.
then i checked and the test family was ok with own parents...
then i copied the first row A to be B and the result: wrong mom and dad...

this is the formula i use:
&ALS(EN(BR15<>"";BS15<>"");"moeder & vader zijn "&BEGINLETTERS(INDIRECT("N"&BR15))&" en "&BEGINLETTERS(INDIRECT("N"&BS15))&". ";"")

attached two pictures: you will see the first rows being identical, in column F you will see the names of mother and father.
the second pictures shows only one time the first row, after deleting the following rows, and check the names of the parents: different !!!

i guess nothing is wrong in my formula, but why names are changing then ???

and then the pictures are too large grrrrrrr (sorrrrrry)

 

[Akuini]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

 

[littlepete]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

hello,
thank you !!!
i will make all names and numbers fake and send you a onedrive cloud link !

 

[littlepete]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

hello

adressenlijst 29 mrexcel.xlsm

Microsoft Excel Workbook

1drv.ms

here we go ! thank you so much, i really hope you can find the solution...
most of the combinations mother father child have of been ruined but you can add parents and children to anyone !!!

good luck !!! it's very valuable to me !!!
Peter

 

[Akuini]

Ok, I've downloaded your file.
Could you give me an example, step by step, what I should do to test the problem?
Remember I don't understand your data, so you have to be detailed.