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]

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.

ok :
each row is a person, or sometimes an event. you will find royalty in it too.
columns a to f are seen on the screen, they are a representation of the columns k to ba where yo can fill in the data.
when you open the file it will automatically start a macro filtering all data happening this month, and then scroll to the first
thing happening today or in the future.
to see the whole list: F10. to find a person: control-p. above all columns a-f in the green row 4 you can see the function key to filter
based on what you read in that column.
if you have the whole list F3 gives you the same result as when you open the file.
column F is the column that could have this result:
"peter is born on tuesday 19 april 1961. he died in brussels on wednesday 15 may 2000. he got married in paris on (...). his parents are Anna and Paul."

that is the fomula where, completely at the end before the last block of five lines you can find this:

&ALS(EN(BR141<>"";BS141<>"");"moeder & vader zijn "&BEGINLETTERS(INDIRECT("N"&$BR141))&" en "&BEGINLETTERS(INDIRECT("N"&$BS141))&". ";"")

that is where the problem is. beginletters is starting capital for a name; moeder and vader you can guess hahaha...
the data are in column N (first name); O (last name); BR (mother) and BS (father).
need to know more? just ask and thank you so much!!!

 

[Akuini]

Sorry, I don't think I can help you with this. The workbook has so many formula & they are long formula, so it's hard for me to understand.
But I don't think you need INDIRECT, because your formula basically always refers to another cells on the same row. If you use INDIRECT then you will get wrong result if you add or remove a row above it.
But as you said on your original formula (without Indirect):

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't figured out why that happened, actually I can't figure out a way to test it.

&ALS(EN(BR141<>"";BS141<>"");"moeder & vader zijn "&BEGINLETTERS(INDIRECT("N"&$BR141))&" en "&BEGINLETTERS(INDIRECT("N"&$BS141))&". ";"")

I don't understand in the formula above, what is "N" for?
And I can't find "BEGINLETTERS" function in your code module.

On a side note:
You set up some shortcuts using Application.OnKey on F3 to F12. I really suggest that you reset these keys to their original function when you close the file, otherwise it will be functional on any open excel file. You can add this Sub in Thisworkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Application.OnKey "{F3}"
    Application.OnKey "{F4}"
    Application.OnKey "{F5}"
    Application.OnKey "{F8}"
    Application.OnKey "{F9}"
    Application.OnKey "{F10}"
    Application.OnKey "{F11}"
    Application.OnKey "{F12}"
End Sub

 

[littlepete]

Sorry, I don't think I can help you with this. The workbook has so many formula & they are long formula, so it's hard for me to understand.
But I don't think you need INDIRECT, because your formula basically always refers to another cells on the same row. If you use INDIRECT then you will get wrong result if you add or remove a row above it.
But as you said on your original formula (without Indirect):


I can't figured out why that happened, actually I can't figure out a way to test it.

&ALS(EN(BR141<>"";BS141<>"");"moeder & vader zijn "&BEGINLETTERS(INDIRECT("N"&$BR141))&" en "&BEGINLETTERS(INDIRECT("N"&$BS141))&". ";"")

I don't understand in the formula above, what is "N" for?
And I can't find "BEGINLETTERS" function in your code module.

On a side note:
You set up some shortcuts using Application.OnKey on F3 to F12. I really suggest that you reset these keys to their original function when you close the file, otherwise it will be functional on any open excel file. You can add this Sub in Thisworkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Application.OnKey "{F3}"
    Application.OnKey "{F4}"
    Application.OnKey "{F5}"
    Application.OnKey "{F8}"
    Application.OnKey "{F9}"
    Application.OnKey "{F10}"
    Application.OnKey "{F11}"
    Application.OnKey "{F12}"
End Sub

that's a good suggestion !!! thank you for helping ! if i find the solution i'll let you know here !!! have a nice sunday !!!

 

[eastrand]

@littlepete, the link for this file is no longer active. Can you either post the data via XL2BB or post a current link to the file?

 

[littlepete]

@littlepete, the link for this file is no longer active. Can you either post the data via XL2BB or post a current link to the file?

if you check post number 9 you 'll see attached a little file with fake data to test and try

 

[eastrand]

That link no longer works. This is what I get when I click on it.

 

[littlepete]

adressen mrexcel.xlsb

Microsoft Excel Workbook

1drv.ms

this link should work, i placed the file on his original place

thanks !

 

[eastrand]

Wow! A whole lot of stuff to look at and some really long formulas. It looks like all rows after row 4 are the lookup rows. Where are you trying to add a row? Are you using a macro to add the row or right clicking and selecting insert or some other method? I don't have much more time to look at this today but will look over it this week.

 

[littlepete]

Wow! A whole lot of stuff to look at and some really long formulas. It looks like all rows after row 4 are the lookup rows. Where are you trying to add a row? Are you using a macro to add the row or right clicking and selecting insert or some other method? I don't have much more time to look at this today but will look over it this week.

hello

thanks for looking into it !

each row is a person, when i add a row that is a person i know and i have data of him.
columns A - F are representation to fit on the screen. the data themselves are in columns K - CR.
I have a starting macro (auto open) to filter all persons with an event happening in the current month
and then scroll through that list to put a first next event putting at the top.

column B presents the name (columns K to P). column Ax is mother's name. column Ay is father's name.
i have named ranges on each mother or father in column B as like :
peter hunter => " oupetehunt " four first of the first name, four first of last name...
each child in the list gets a hyperlink in column ax for mom, ay for dad.
if no rows are added those work perfectly... but when rows are added i receive links going 1, 2, 3 rows lower or higher...

that's what i would like to resolve...
so thank you for your time !!!

 

[eastrand]

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