Navigation linking within sheets, possible?

[StargateFanNotAtHome]

I know that we can hyperlink to URLs and even files on a computer,
etc., but is there a way to hyperlink as a navigation tool between
worksheets?

I have too many entries to do up scripts and buttons to do this, and
was hoping to avoid the hyperlink feature that you do via the context
menu. When I do things that way, yes, I can jump to the sheet but it
selects the print area.

This is what I tried, which didn't work:

=HYPERLINK(='7020-303-4103'!, ">>Go To>>")

I added an extra, small column and put that into the line corresonding
to the data found in sheet 7020-303-4103.

Excel tells me that it, "Cannot open the specified file.", when this
link is clicked.

Thanks! D

 

[Dave Peterson]

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

 

[StargateFanNotAtHome]

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

Hmmm, nope. Couldn't figure out how to get these to work. I
substituted every which way I could think for for "sheetone!" but I'm
either doing something wrong or wires are getting crossed somewhere
<g>.

Why won't something simple like this work, anyone know?: =HYPERLINK
("7020-303-4103", ">>Go To>>")

I've tried the sheet name by itself in my example here, with quotation
marks and exclamation point on and off and with and without the equal
sign. Is hyperlinking not meant for sheet navigation at all, then?

Thanks! D

 

[Dave Peterson]

=HYPERLINK("#"&CELL("address",'7020-303-4103'!A1),"Click me")

Will take you to A1 of that sheet.

 

[StargateFan]

=HYPERLINK("#"&CELL("address",'7020-303-4103'!A1),"Click me")

Will take you to A1 of that sheet.

This all turned out really, really well. I now have a workbook to
which I added a ton of navigational links to so that it's a helluva
lot easier to work with. The challenge is that for every entry, we're
need to go to two spots, one of two "index" sheets at the beginning,
and then a jump to the actual spreadsheet that needs the invoicing
details. Easy now since each row has a jump "formula" in it using
this hyperlink approach here to get to that details sheet for each
financial category.

Then at the top of each sheet, I have a "return to sheet A" and
"return to sheet B" type of reference so that it's easy to jump back
to one of the first two "index" sheets.

Each spreadsheet has 1 to 3 or more financial categories in them.
Since I used a $A4 type of half absolute cell reference, those links
that need to jump to a reference other than $A4 should adjust properly
as entries above them are added. In other words, if a second category
for a particular financial code started at, say, cell reference $A21,
as rows are added above A21, that jump reference should adjust
automatically in that brilliant way that Excel has of working with
relatiive cell references so that the link should not need editing
again.

Thanks. D

 

[Dave Peterson]

The worksheet function makes that kind of thing easy. But you could insert a
name (insert|Name|define) and then the insert|hyperlink would work ok, too.

But I like the formula approach.

 

[StargateFan]

The worksheet function makes that kind of thing easy. But you could insert a
name (insert|Name|define) and then the insert|hyperlink would work ok, too.

Thanks, Dave! I didn't understand all that yet, but will definitely
take a look at it. You guys have such patience with us dumb-old
users, eh!! <g> But I always love learning a couple of approaches in
case down the road something doesn't work exactly as needed yet a
second approach does.

Thanks! D