3

Background: I'm the author/maintainer of xlrd, a Python package for reading Excel 97-2003 XLS files. I'm in the process of enhancing to extract information about hyperlinks. My reason for asking this question is to optimise the memory/CPU tradeoffs for the common case, while functioning in the same way as Excel

In Excel 2003 and 2007 (and presumably 2010), it is possible to attach a hyperlink to a single cell; this is well known.

Excel 2003 and 2007 also allow you select a range for insertion, e.g A1:C3. In that case, clicking on any cell in the range will jump to the target of the hyperlink. I can't find any web reference to using a range.

My question is: Does anybody use the range facility? What for?

A possible use case: The first worksheet is a menu for the remainder of the workbook. Each worksheet or topic has a hyperlink on the menu sheet. Each hyperlink occupies a 3x3 range of cells to make it easier for users in a hurry to click on the correct link.

2
  • @studiohack: Well that was waste of time, wasn't it? Mar 20, 2011 at 22:07
  • depends on how you look at it; your question was vague and it was hard to tell exactly what you were asking, it was ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. Please clarify and flag for mod attention; I'll be happy to reopen once improved.
    – studiohack
    Mar 20, 2011 at 22:10

3 Answers 3

1

I do not presently use it, but... maybe I will. Did not realize it would work like this.

Must use @ after = nowadays with SPILL functionality. Also, if it gives a #VALUE! error instead of the "nice name", it doesn't matter to the functionality of the hyperlink. Though in all cases, I had to double click rather than single click like usual or use Ctrl-Click like in some cases.

As to what I'd use it for? Currently I often have range areas in a worksheet that get data, then need cleared for a new use of the space. I give the entire area, discontinuous or continuous, a Name, then select that range in the box to the left of the F2-Formula Editor. It's highlighted and I press Delete clearing it all nicely. Take care setting the Name up and you're in the cell you want to start in, or one below, say, so you can arrow up clearing the selection of the area and ending up in the right starting cell. You get the idea.

But people don't like it for various reasons, all nitpicky. Still, they won't use it. So, useless, except to me.

One can even set this up putting a formula in a cell to select (select that cell), then hit F2-Edit and the dependents are highlighted. Delete wipes them out. So write the formula right and it does the same thing. People don't like that either.

(There's a point where you aren't "just too clever" and it's really the people are stupid.)

Perhaps this. The cell formula would do the highlighting and if you put the range in parentheses (()) it will take any discontinuous range you like. (I only experimented with unions (commas between cells and such) not intersections, but it seems it ought to use any range mechanism.)

So I will try this though I understand it's a... little late... for the answer to be useful to the poster's purpose.

The one thing I don't see it doing is being relative rather than absolute. Even with relative referencing in the cell, it only became relative if I copied the hyperlink cell on the sheet (so it followed the usual conventions) but then had to have X-number of cells each with different formulas so the mechanism used the relative references like usual, but isn't really relative.

What I mean by that is let's say (another use on the same theme), you always want to select a set of cells in a row and maybe one or twelve others for working with the data. Maybe you're checking eight cells in one row and three in the row above, and use the highlighting to show the ones of interest, as well as move easily with Enter between them (or Tab). (Even more useful when there might be two screens of space left-right between two of them.) Then want to move down a couple or 600 rows and do the same thing. With the Named Range approach I mentioned first, you can use relative references in the Named Range so you get the same relative set each time you click them: relative to where you are working, but the same pattern. And some can STILL be absolute in case you need that too.

Also useful using a trick to sense your current selection, but that's something else altogether.

The Named Range approach works that idea wonderfully, but the formula approach and this hyperlink idea would not. So... limits...

0

I think it'd be useful to create your own custom buttons out of cells, so that clicking anywhere on the button, which would cover the range, would take you to the intended destination.

0

I have never used it, however I could see it reducing workload and errors when editing a column of hyper-links. If it allows for multiple entries like A1:A5,A7,A9:A12 it would be even better. (I am thinking of some table of suppliers/products or the like that could have a large number of links to the same location)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .