in Calc how do you create an INDEX on first worksheet of all other worksheets in the workbook and its hyperlinked so you can jump to and back to main INDEX worksheet, How do you do this? (I've done it in excel which works perfect)

[Satz2908]

in Calc how do you create an INDEX PAGE on first worksheet of all other worksheets in that workbook and its hyperlinked so you can jump to and back to main INDEX worksheet, How do you do this? (I’ve done it in excel which works perfect)

I have a workbook of 254 worksheets, all sheets are custom named for each category…

So, I would like to be able to search for the worksheet in the INDEX Page and jump to it, then click “RETURN TO INDEX” which brings me back to the INDEX PAGE…

So how can I do this in LibreOffice Calc…?

[mikekaganski]

I’ve done it in excel which works perfect

Please describe how did you do that in Excel, so that it’s obvious if something similar is available in Calc. Or give a link to the function’s documentation.

[Satz2908]

Hi,

Around 2 years ago, i followed this tutorial guide :

Automate Building A Table Of Contents For Your Spreadsheet With VBA —...

…it worked perfectly well.

Can you try make this work on LibO?

It will be soo amazing if you can…will really help me and I’m certain many others too out there Thx!

[erAck]

There’s no automatic way. Either insert the jump target as hyperlink (Ctrl+K or menu Insert → Hyperlinks…) or use a push button with Action Open document and URL, for both use a fragment of the jump target’s cell address as URL, e.g. #Sheet2.A1. I’m pretty sure creating the index and buttons for 254 sheets can be automated using a macro or extension, maybe someone wants to write one.

[Satz2908]

Thanks for your input erAck,

May someone “more skilled than I at present” please write a macro to do this (or guide me how to do it myself ) ?

i.e. create an Index Page on the first worksheet, this will list all the worksheets in the workbook hyperlinked which will jump to that worksheet and then in that worksheet a “RETURN TO INDEX” cell will appear in say cell E1 (Top middle of the screen) …so you click this and it returns you to the INDEX PAGE worksheet to jump to the next worksheet you wish to work on…

Please help me out guys…guide me on how to acheive/do this so I may learn too

Thank you
Satz.

[Lupp]

As far as I know there is no standard function returning the name of a sheet without your previous knowledge. The SHEET(Reference) expression returns the number of the sheet (counting the tabs e.g.)

To write a user function in BASIC for the task is simple.
You may also automate the creation of links to given cell ranges…
The jump back to the last previous selection in the index sheet is rather banal. You may assign a respctive routine to an additional element of one of the toolbars. It can’t work, however, as long as not such a selection was stored.

Using the HYPERLINK() function, you will need two cells per target range: one describing the range by its content, one creating the link with the function. Since LibO Calc has implemented the .Hyperlinkproperty for SheetCell objects, you can automate a process using only a single cell per target. There may be some flaws in the implementation. Not every link created that way works eaxctly as expected immediately after a reload.

The below attached demo uses the sheet events Content changed and Selection changed for both mentioned purposes. Just play with t and rework it regarding your needs.

ask214922getSheetNamesIndexAndLinkCellsToSheteRanges_1.ods

BTW: Are you sure a Calc document with more than 200 sheets is well designed (an the solution is using the appropriate tool)?

[Satz2908]

Hi Lupp, to insert sheet name in a cell, I double click the cell, then click insert (on toolbar) → Field Name → Sheet name and it populates the sheet name in cell A1…

So for now, I can painstakingly create a manual index in this way…but I prefer the way excel does it with a macro…

In excel the moment i create a new sheet and go to the index page(refresh formula’s) the newly created sheet is automatically added to the index page which is refreshed continuously…and keeps my workbook up to date…

Since I am trying Libreoffice…this feature is holding me back…if I can get this to work on LibO…I will immediately ditch MS.O and be a dedicated LibO fan for life!!

Well done to all the designers/coders for creating LibO…I like it a lot so far…just need more video’s /walk through’s from you guys on how to do certain tasks/things in order to fall in love with it now…

Feel free to guide me to resources to learn how to get the most out of LibO.

regards,
Satz.

[Lupp]

I don’t know recent Excel (used no Excel for about 20 years now) but as far as I understand your explanations, the user code demonstrated in above example should show very nearly the same behaviour.
Please xplain in what way its is insufficient for you.
If you didn’t see the code work, you may have set “high macro security”. Withd “medium” you are prompted for permission to xecute document macros.

[Satz2908]

Long semi-solution:
Double click cell A1 in each sheet, click insert, field, sheet name… then create INDEX PAGE on sheet 1 of workbook, reference to each worksheet"name".A1 then hyperlink them…

Then also create a jump “BACK TO INDEX” and hyperlink it to the main INDEX PAGE.

This can work but has some draw backs and will take a long time to implement…

I will really appreciate if someone more skilled can guide me on how to do a macro that can automate the routine tasks and also update the main index page with the new sheets created…(in the middle, begin, end, random places created new sheets in the workbook)

This helps me make my work really efficient and keeps everything in the 1 master workbook I use…please help me achieve my objective to work more productively.

Thanks guys.
Satz.