How to create a series of hyperlink buttons?

English
#1

Relevant example sheet uploaded. Button Shortcuts.ods (18.6 KB)

I would like to create a series of hyperlink buttons that will jump to cells in specific ranges within my sheet. As in the example, I have manually created 4 buttons that jump directly to cells in the first 4 ranges.

Is there any way to create the remaining 6 buttons by copy & paste, or with a formula, such that they will be correctly linked to ranges 5-10 without manual editing?

In my final product I will have more than a hundred ranges, and would like matching “jump to” shortcuts for each. It seems ridiculous to have to manually create links o each range. Is there a better solution?
TIA

#2

Does it have to be a button? Is it just the link that doesn’t work?

=HYPERLINK("#F2:G5";"Jump to Range 1 (F2:G5)")
or
=HYPERLINK("#F8";"Jump to Range 2 (F2:G10)")
#3

Same idea exemplified:
disask83742jumpUsingHYPERLINKfunction.ods (14.2 KB)

.
FormControl objects aren’t made for such purposes. Using the HYPERLINK() function you can easily calculate the target cell (or range).

2 Likes
#4

  • It must be a link that doesn’t require a user to hold CTRL while clicking, so a button is the only option I am aware of

  • My trouble is not creating a link or button, that works. The challenge here is to recreate a larger number of working links for a lot more ranges using a formula that can be repeated. As I mentioned, in my final product I will have over a hundred buttons/links, and I hope it will not be necessary to create and edit every one of them manually.

#5

Having seen your sheet a general advice: Reconsider the design. What are you attempting to achieve using the graphically formatted ranges? If there shall be some input, a few calculations and… I would assume you also want to evaluate the partial results (per range) somewhere by formulas.
Non-contiguous cell-ranges tend to complicate any overall evaluation extremely.

#6

Thank you for the example. It’s very much what I was looking for. Is there no way to have this hyperlink appear as a button? Instead of a ctrl-click?

#7

@Lupp, I’m sorry, but it’s easier for me to link to your answer than to write this warning about the danger of clicks without Ctrl again …

#8

@JohnSUN Thank you for that info. It’s one reason I was hoping explicitly for a button, which would make it more clear to users that it’s going to do something.

#9

@JohnSUN and myself understood that well. The problem is that FormControls (or ordinary shapes also capable of taking a hyperlink which I would prefer) won’t accept formulas for recalculation. Thus I think you would need CustomCode Either for creating the shapes and assigning the hyperlinks or for calculating a hidden formula on demand.
What about the “every-sixth-row” design. An “every-row-design” wouild simply make vanish the issue.

#10

I was considering whether to mention that the related setting was removed from the UI, but is still available. It’s actually dangerous (though still default in AOO?).
I still don’t feel sure about what the questioner finally wants to achieve. Of course, you and me can write the needed code for the creation or on-the fly- adaption of buttons using another 20 min, but what comes then?

#11

And then the user will scroll up and down the page looking for one of the hundreds of buttons…

#12

@JohnSUN & @Lupp Thanks for your continued help. At the risk of extending this thread with an unrelated issue, I’ll try to be more clear about why I’m trying to achieve this goal.

This question came about because I’m trying to make my already-existing worksheet more simple to use for other users.

In SHEET1 users enter record data, one row at a time. Each row/record has a related table available for optional data. As users complete each row/record, they will have the ability to click a button/link in that row which will take them to the related table in SHEET2, where the additional data can be entered. A button/link in that table will be able to return them to their row/record in SHEET1. The whole purpose of the hyperlink is for ease of navigation.

Would the spreadsheet be much more appropriately designed as a database? Yes. But I have so far been incapable of that, as much as I have tried to learn. Plus, the hope is that eventually this sheet may be ported to Google Sheets so that it can be accessed in the cloud, and on mobile devices, similar to having an app.

I welcome any advice and suggestions, either here or via message, and thank you again.

#13

In other words, you need no more than a hundred buttons, but only one button that will crawl across the sheet after the user (behind the focus of the active cell), always be in the current row and always take the user to the right place (always different) on the sheet of auxiliary tables? Somewhere I recently came across something similar…

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.