Ask Your Question
0

How to create a ToC for Calc file with dynamical data

asked 2020-08-14 13:54:36 +0200

S. N. gravatar image

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.

image description

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2020-08-14 16:10:31 +0200

dscheikey gravatar image

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: C:\fakepath\Hyperlink_Name_ABC.ods

If this answered your question, please mark as answered.

edit flag offensive delete link more

Comments

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

S. N. gravatar imageS. N. ( 2020-08-14 16:45:48 +0200 )edit

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

=CHAR(CODE("A")+ROW()-1)
Mike Kaganski gravatar imageMike Kaganski ( 2020-08-14 17:40:11 +0200 )edit

@Mike Kaganski Good to know. Thank you.

S. N. gravatar imageS. N. ( 2020-08-14 18:20:40 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-08-14 13:54:36 +0200

Seen: 63 times

Last updated: Aug 14 '20