How to create a ToC for Calc file with dynamical data

[S.N]

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.

[dscheikey]

Hi S.N.

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

=HYPERLINK("#A"&MATCH("A"&"*",$A:$A,0),"A")

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.

[S.N]

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

[mikekaganski]

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

=CHAR(CODE("A")+ROW()-1)

[S.N]

@mikekaganski Good to know. Thank you.