Forum Discussion
Sorting named cells - trier des cellules nommées
Oups, je suis donc bien sur le forum en français !
Donc voilà j'ai un petit souci.
J'ai un fichier avec deux onglet. sur le deuxième j'ai une liste de contacts.
Dans la colonne a j'ai a chaque fois nomé ma premiere cellule avec le nom du contact.
Dans le premier onglet j'ai dans les différentes cellules des liens interne vers le deuxième onglet. Ces lien pointent vers les nom de cellule que j'ai entré dans le deuxième onglet et tout se pass bien....jusqu'a ce que je trie les lignes dans mon deuxième onglet (par ordre alphabetique par exemple)
Olors tout est foutu et la cellule appelée Durant correspond maintenant a Dupond et donc plus rien ne fonctionne.
Je suis donc a la recherche d'un système qui me permet de pointer vers une cellule sans que son nom ne soit affecter par le tri (ou filtrage ) que j'applique.
Suis-je clair?
Merci pour votre aide
Hi,
I got a little issue here !
I got one spreadsheet with two tabs.
On the second tab I have a list of contacts. The first cell of each contact has a name so I can use that name to include hyperlinks on the first tab to reach the right cell on the second tab.
Infortunately each time I'm sorting the list of the second tab everything is messed up and the name I give to the celles is not attached to the cell anymore.
I want to be able to point to a contact that is listed on tab 2 from tab 1 no matter how much I rearrange the list of tab 2.
Would appreciate some help here
6 Replies
ChristianDel I think I understand what you are doing.
You are defining a 'NAME' for each row in sheet 2. For example you might have a name called
'Amy' defined as ='Sheet 2'!$A$1 because that is the row where 'Amy Smith' is located
then in Sheet 1 you have some link pointing at the Named location called 'Amy'
The problem is that you sort Sheet 2 and the named location 'Amy' no longer has 'Amy Smith'
Is that right?
Maybe try having your NAME definition smart. For example:
'Amy' could be defined as =INDEX(Sheet2!$A:$A,MATCH("Amy",Sheet2!$A:$A,0))
of course what you use in that name definition will have to be unique.
Hi,
Thank you so much for giving me a bit of your time.
Yes you understood it right. But I didn't understood your solution ;-(
Find attached a sheet with some data to show the issue.
On the second tab I have this list of names. Each cells in column A has a name as per its content.
If I sort this list alphabetically as per the first column everything gets mixed up.I tried to convert your proposition into French coding but then again I don't know where to include this complex reference. Not as the name of the cell and not as its reference. both got rejected.
Would you be so kind to give me that extra help?
Thank you
ChristianDel I don't know if translations will cause an issue but the solution I proposed was putting that formula in the 'Name Manager'. See here:
I did it in the sheet you sent (see attached) and it works for me, I hope the translation thing doesn't break it.
ChristianDel Can you post a sample spreadsheet with a few rows of data (not your original file)? It's hard to tell you what to change if we cannot see what you have in place.
Hi,
Thank you so much to give me a bit of your time.
Please find attached a file that would illustrate my issue.
all the cells in tab 2 are named as per their contend.
If I now sort this list alphabetically as per the first column, the name definition of the cells got mixed up and do not correspond to the right content anymore.
Any idea how I could do that better?
Thank you
