Excel 2007 and Hyperlinking Question

basilides · #1 11-21-2009, 12:52 AM

Greetings! I am using MS Windows 7 and MS Excel 2007. I am not very adept at using the Excel program yet, so I couldn't find an answer to this question on my own on the Internet or in the Excel help files.

I have an Excel sheet named "Table_of_Contents" that has links (about 100) to various cells in another Excel sheet named "Listings." A problem obviously arises when I add row(s) or delete row(s) to the "Listings" sheet. The links to cells from "Table_of_Contents" lose their correct places (cell locations) in "Listings." Is there any way to get around this problem?

It is very laborious to have to go to "Table_of_Contents" and change every hyperlink to the new cell positions in "Listings," which is what happens when rows are added or deleted. I hope a solution to this does not involve creating a complicated macro because I don't how to program.

DonkeyOte · #2 11-21-2009, 02:34 AM

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.

basilides · #3 11-21-2009, 07:28 AM

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?

DonkeyOte · #4 11-21-2009, 12:53 PM

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 ?

basilides · #5 11-22-2009, 04:49 AM

Lord, have mercy!

Sheet 1 contains cells (featuring three-letter bible book abbreviations: Gen, Exo, Num, etc.) with hyperlinks to various cells that have been bookmarked in Sheet 2 in various rows in Col. A. Below is a sample of what Sheet 1 looks like (basically, it's a simple Table of Contents):

Gen (row 1, col A) Exo (row 1, col B), etc.
...
Jer (row 20, col A) Eze (row 20, col B), etc.

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.

If I add or subtract rows in Sheet 2 (where are five columns: Col. A contains the Greek words, Col. B, the Greek lemmas, etc. -- note: only the first cell [row 33333] is Exodus is bookmarked, of course), the setup (TOC) in Sheet 1 will obviously be disrupted, and require tedious modifications (one by one) of almost all the hyperlinks (assuming there isn't a better way to do this)

If this is still unclear, then just ignore it because I lack the ability to express myself clearly, perhaps due to advancing senility. Hallelujah!

DonkeyOte · #6 11-24-2009, 08:40 AM

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 ?

Quote:

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.

Welcome to the Excel Forum

Forum Statistics