Thread: Excel 2007 and Hyperlinking Question

Realistically we'd need to know what the logic is that determines the targets.

You could for ex. possibly use the HYPERLINK worksheet function to create the links in such a way as they adapt.

Unfortunately we can't be any more specific as we have nothing specific on which to base our responses.

If you're able to post a small representative sample of your file and the links in question please do so.

Greetings, again!

Sorry I was not more specific in my original post, but I am relatively new to the program. However, this tedious explanation might bore you.

I am using the Excel 2007 hyperlinks more or less as bookmarks. Now you will undoubtedly criticize me for the way I am (mis)using Excel 2007, and not using a database or other more suitable software (which I have tried already, by the way), but please bear with my peculiarity. There is a reason for this madness. A long story, though.

Briefly, on the first sheet (page) I have columns and rows containing hyperlinks to various bookmarked places in the Greek Bible (Septuagint) contained on the next sheet. So on the "Table of Contents" sheet, there is "Gen" in the first cell (row 1, col. A), "Exo" in another cell (row 1, col. B, etc.,) on the same sheet. When I click on the cell with "Exo" in it, I am taken to the next sheet which contains the entire Greek Bible (in columns, word by word, one row for each word, interlinear in format) and I am at row 33432 where the first Greek word in that book is located.

The same (second) sheet contains all the other books, one word on each row (hence, over 1 million rows!). The various columns contain the Greek, the Greek lemma, a parsing, an English translation, etc.

Now, as I mentioned in the original post, when I revise the (second) sheet with all the rows of Greek words, say to add or delete a Greek word (not all Greek manuscripts carry the same weight, so to speak), all the hyperlinked bookmarks become out of whack. They can be changed, one by one, but that is very time-consuming. Is there anyway to surmount this problem?

So do the columns on summary sheet and source sheet correspond ? ie Greek in Column A etc...

If you could post a much reduced version we might be able to help by means of demonstration, I'm guessing you'd be equally as happy to enter formulae in the summary sheet as opposed to entering just the word and/or manually inserting the hyperlink ?

I wouldn't say it was unclear rather I would just reiterate the point regards sample files - generally speaking it's much harder to comment / determine possibilities when we're at best "visualising" your set up as best we can... and though your narrative is detailed I do still have one key question...

That question is whether or not there is any discernible logic governing the linked cells that XL could attempt to replicate ?

Originally Posted by basilides

If I click on the hyperlink Exo in Sheet 1 (row 1, col B), I am taken to row 33333, col A in Sheet 2, where is the first Greek word in that book appears.

In the above you see it's not clear what the logic is other than "first greek word"... this (first greek word) is likely to prove difficult for XL to replicate / automate.

If indeed the process of linking is determined manually (ie using human skill) then I'm afraid (IMO) realistically "trapping" an event like rows / columns being added/removed from a sheet is difficult... more so when you consider that in addition to trapping the event you also need to determine exactly which rows were added/removed such that all links tied to rows below those affected can have their references adjusted accordingly.... non-trivial.