Forum Discussion
Sorting named cells - trier des cellules nommées
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.
Waww ! Now I got it, brilliant !
However...As I'm trying to add a few names in the list I could add the string athe right place on tab 2 but failed to figure out what I should include in tab 1.
Originally when I wanted to add a link it would propose me the list of names created for me to select but here it didn't worked.
As you can see the defined name list isn't expandable for me to choose the right one from. Did I missed something? How did you create the link?
Other small issue and this one might, or not, be more complicated to sort out since I remember you said the names needs to be unique.
In some cases on my real table, it does happen that I have more than one contact associated with a company. To be honest since I'm now able to sort my list, I can ignore the other entries under the same company name since after the sorting process all of those entries are likely to be one after the other in my list. Any advice?
Last thing The process of creating the smart reference is highly time consuming. Would there be a way to create a cell in my table that would concatenate the bits and pieces and use the content of that cell as an automatic way to create the reference for the name?
I understand I dragged you into a trap here but be sure I highly appreciate your valuable help.
Thank you