excel click on first cell to find cell in other column with same value

[littlepete]

Hello all

I have given up on named ranges !!! they keep putting $ in front of rows and columns in the n.r. definition, result:
when rows are added half of the named ranges are wrong !

each row is one person, with his first name M and last name N ; mother in Ax and father in Ay.
of course mother and father are in the list too.

i have tried with named ranges to make the mother and father clickable links and they do end up on the row of mom and dad !
(as long as no rows are added !!!)
to make it easy to use, in the visible data of a person with links to mom and dad, i have put "links" so i can see there are links
hiding all columns between name of the person and the column Ax makes it still easier to go to mom's or dad's row

is there a good way to make sure when i click on Ax column with mom/dad's name, even after adding rows
i will end up on the row of mom and dad ???

thank you and have fun excellating !

 

[BSALV]

something like this ?
so there is a hyperlink between "little pete" and "Big Pete" and "Mom Pete" (are the names of the parents in 1 cell ?)
I prefer doubleclick or rightclick on the name of a parent and VBA handles to jump to the parent's row.

littlepete.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	firstname	lastname	a	a2	a3	a4	a5	a6	a7	father	mother
2	little	pete								Big pete	Mom Pete
3	s
4	s
5	s
6	s
7	s
8	s
9	s
10	s
11	Mom	Pete
12	s
13	s
14	s
15	s
16	s
17	Big	pete
18
Blad1

 

[littlepete]

you can see a little version here:

adressen mrexcel.xlsb

Microsoft Excel Workbook

1drv.ms

good luck !!!

 

[BSALV]

littlepete.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
1	firstname	lastname	a	a2	a3	a4	a5	a6	a7	father	mother
2	little	pete								Big pete	Mom Pete
3	s
4	s
5	s
6	s
7	s
8	s
9	s
10	s
11	Mom	Pete
12	s
13	MomPete	2
14	s
15	s
16	s
17	Big	pete
18
19
Blad1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     If Not Intersect(Target, Range("TBL_Namen[[father]:[mother]]")) Is Nothing And Len(Target.Value) > 0 Then     'er is dubbelgeklikt in father of mother
          a = [transpose(TBL_Namen[Firstname]&TBL_Namen[lastname] & "|" & row(TBL_Namen[lastname]))]     'array met firstname & lastname & "|" & rownumber
          sp = Split(Target.Value)                              'split parentname on spaces
          fl = a                                                'copy to auxiliary array
          For i = 0 To UBound(sp)                               'loop through all the parts of the parent name
               fl = Filter(fl, sp(i), 1, vbTextCompare)         'filter if that part of the name is still in it
               If UBound(fl) = -1 Then Exit For                 'exif for is there are no longer possibilities
          Next

          If UBound(fl) < 0 Then                                'nothing left
               MsgBox "vader/moeder niet gevonden", vbCritical, UCase("volgende keer meer geluk")
          Else
               rij = Split(fl(0), "|")(1)                       'last part in 1st match is the rownumber of the parent in firstname&lastname
               Application.Goto Range("A" & rij)                'goto that cell
               If UBound(fl) > 0 Then MsgBox Join(fl, vbLf), vbInformation, UCase("Meerdere mogelijkheden")     'msgbox in case of multiple possibilities
          End If
     End If
End Sub

Too messy to find something in your workbook, so i created a small example with a table and a "doubleclick"-event in the column of the father or the mother.

 

[littlepete]

littlepete.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
1	firstname	lastname	a	a2	a3	a4	a5	a6	a7	father	mother
2	little	pete								Big pete	Mom Pete
3	s
4	s
5	s
6	s
7	s
8	s
9	s
10	s
11	Mom	Pete
12	s
13	MomPete	2
14	s
15	s
16	s
17	Big	pete
18
19
Blad1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     If Not Intersect(Target, Range("TBL_Namen[[father]:[mother]]")) Is Nothing And Len(Target.Value) > 0 Then     'er is dubbelgeklikt in father of mother
          a = [transpose(TBL_Namen[Firstname]&TBL_Namen[lastname] & "|" & row(TBL_Namen[lastname]))]     'array met firstname & lastname & "|" & rownumber
          sp = Split(Target.Value)                              'split parentname on spaces
          fl = a                                                'copy to auxiliary array
          For i = 0 To UBound(sp)                               'loop through all the parts of the parent name
               fl = Filter(fl, sp(i), 1, vbTextCompare)         'filter if that part of the name is still in it
               If UBound(fl) = -1 Then Exit For                 'exif for is there are no longer possibilities
          Next

          If UBound(fl) < 0 Then                                'nothing left
               MsgBox "vader/moeder niet gevonden", vbCritical, UCase("volgende keer meer geluk")
          Else
               rij = Split(fl(0), "|")(1)                       'last part in 1st match is the rownumber of the parent in firstname&lastname
               Application.Goto Range("A" & rij)                'goto that cell
               If UBound(fl) > 0 Then MsgBox Join(fl, vbLf), vbInformation, UCase("Meerdere mogelijkheden")     'msgbox in case of multiple possibilities
          End If
     End If
End Sub

Too messy to find something in your workbook, so i created a small example with a table and a "doubleclick"-event in the column of the father or the mother.

the ease and speed when you write that stuff !!! wow !!!

 

[littlepete]

CAN PLEASE PLEASE PLEASE

someone help me with my named ranges...

i correct them ; save the file ; check them ; looks all ok ; use them and click on it ; value totally changed !!!!!

i am getting very angry and crazy here !!!
i read everywhere the same and noone gives me the solution to a very simple use of a named range !!!

please

 

[BSALV]

i downloaded your file and used it locally on my computer.
I saw nothing in VBA to create "named ranges" there, so you do it for all of them manually, i suppose.
Then you do something and they are changed or gone.
What could have changed them ? VBA or you ?

 

[littlepete]

i downloaded your file and used it locally on my computer.
I saw nothing in VBA to create "named ranges" there, so you do it for all of them manually, i suppose.
Then you do something and they are changed or gone.
What could have changed them ? VBA or you ?

hello

thanks for your reply !

the named ranges change each time after i click on the cell with the link to that named range:

for ex: my row is 2. mom's row is 144. dad's row is 170. our name is put in column B.
name of mom and dad are in Ax2 and Ay2.

the named ranges are made manually. in cell Ax2 (name of mom) is put : LINK : first Lastname and
there the named range is made : " =gegevens!B144 "

when i save all all is ok, i check again; then click and i end up in the next row or row before or 2 more or less...

so i wait now for adding new people because then all will be messed up...

thanks for your advice