How to create a ToC for Calc file with dynamical data

I need advice on what to do with the following problem.

I have Calc file containing a lot of info (thousands of rows). The rows are sorted alphabetically (and can be easily sorted again when new data are appended). One can see a pet example of such file below. I would like to be able to jump to data (names in my example) starting with any letter of ABC quickly.

Ideally, there would be a cell containing the whole ABC in form of hyperlinks, so clicking such a link would result in redirecting to the first cell whose text starts with the corresponding letter (actually, I am talking about a table of contents, sort of).

Data are being added to the file regularly, so an obvious idea of having links to jump to specific cells ist not an option.

Hi S.N.

your idea to create hyperlinks is not so bad. You can use a function with a dynamic structure.


Unfortunately, you only have to create each hyperlink one by one for each letter by hand. At least you have to exchange the A at the beginning of the match command and again in the hyperlink for the name.

I have also built this here once: Hyperlink_Name_ABC.ods

If this answered your question, please mark as answered.

It works quite good. I believe I’ll be happy with this solution. Dankeschön!

Character list may be built in rows 1-26 using this formula:


@mikekaganski Good to know. Thank you.