Sorting merged cells

**lily1030** · 01-08-2016, 12:34 AM

I know what you're going to say, merged cells are the death of excel and I shouldn't ever use them, but hear me out first.

I've made a glossary and included a quick-access sort of system with all the letters of the alphabet up top, but in order to do what I wanted to do (be able to see the entire alphabet and effortlessly click from one word/letter to the other) I had to merged most of my cells, as you can see below.
save.png
(don't mind the ####, I just don't have words starting with those letters yet)

Before merging the cells, I had a macro that would sort the words alphabetically whenever I added a new word (granted it wasn't a great one, but it did the job). Now I'd like to be able to sort my words alphabetically again, but I'm getting errors because the merged cells need to be the same size. Is there any way whatsoever I can go around this? Or another way I can make my quick-access in a way that I can always see all the letters?

Here's my workbook. Any help will be greatly appreciated!
Glossary.xlsm

**bebo021999** · 01-08-2016, 01:29 AM

Try to establish an unique distinct list of A column running accross row 1, then apply proper hyperlink:

B1:=IF(A1="","",HYPERLINK("#"&CELL("address",INDEX($A$2:$A$601,MATCH(0,COUNTIF($A$1:A$1,$A$2:$A$601),0),)),INDEX($A$2:$A$601,MATCH(0,COUNTIF($A$1:A$1,$A$2:$A$601),0),)))

Please Login or Register  to view this content.

....confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Drag accross. Now it is indepedent to AK column. Delete it