Auto-Hyperlink

  • Thread starter Thread starter starguy
  • Start date Start date
S

starguy

i have a problem which seems me very difficult to solve. i have not read
about that on internet.
infact i want to make hyperlink in such a way that when cell whick has
link to other cell copied down the cell it linked to would also be
changed as we observe in coping other functions & formulas.
suppose i have hyperlink in cell "Sheet1!A5" linked to "Sheet2!B5", and
in "Sheet1!A6" linked to "Sheet2!B25". i want that when cell "Sheet1!A6"
copied down it should automatically create hyperlink with cell
"Sheet2!B45".
i want to have this without using VBA.
please tell me if it could be done.

waiting for a solution
regards
 
One play ..

Put in say, C5:

=HYPERLINK("#"&CELL("address",
INDIRECT("'Sheet2'!B"&ROW(A1)*20-20+5)),"Sheet2!B"&ROW(A1)*20-20+5)

Copy C5 down

The above will insert friendly names into C5 down, viz.:

In C5: Sheet2!B5
In C6: Sheet2!B25
In C7: Sheet2!B45
and so on

and hyperlink the cells direct to the destinations
(clicking on C5 will bring you to Sheet2!B5, C6 goes to Sheet2!B25, etc)

----
And if we want to make the actual contents of the destinations
show up as the friendly names in the hyperlinked cells,
we could try this instead in C5:

=HYPERLINK("#"&CELL("address",
INDIRECT("'Sheet2'!B"&ROW(A1)*20-20+5)),INDIRECT("'Sheet2'!B"&ROW(A1)*20-20+
5))

and copy down as before

So if

Sheet2!B5 contains: 100
Sheet2!B25 contains: 200
Sheet2!B45 contains: 300

what we'll see is,

In C5: 100
In C6: 200
In C7: 300

with the hyperlinks continuing to function as before
 
thanks Max it worked well
but this formula is difficult to understand for me. i'll try to understand
it so that i can creat it by myself.
have you any explanation regarding such formulas.

Max said:
One play ..

Put in say, C5:

=HYPERLINK("#"&CELL("address",
INDIRECT("'Sheet2'!B"&ROW(A1)*20-20+5)),"Sheet2!B"&ROW(A1)*20-20+5)

Copy C5 down

The above will insert friendly names into C5 down, viz.:

In C5: Sheet2!B5
In C6: Sheet2!B25
In C7: Sheet2!B45
and so on

and hyperlink the cells direct to the destinations
(clicking on C5 will bring you to Sheet2!B5, C6 goes to Sheet2!B25, etc)

----
And if we want to make the actual contents of the destinations
show up as the friendly names in the hyperlinked cells,
we could try this instead in C5:

=HYPERLINK("#"&CELL("address",
INDIRECT("'Sheet2'!B"&ROW(A1)*20-20+5)),INDIRECT("'Sheet2'!B"&ROW(A1)*20-20+
5))

and copy down as before

So if

Sheet2!B5 contains: 100
Sheet2!B25 contains: 200
Sheet2!B45 contains: 300

what we'll see is,

In C5: 100
In C6: 200
In C7: 300

with the hyperlinks continuing to function as before

---
Click to expand...
 
thanks Max it worked well
Click to expand...

You're welcome, Starguy !
but this formula is difficult to understand for me.
i'll try to understand it so that i can create it by myself.
have you any explanation regarding such formulas
Click to expand...

Here's some explanations ..

One key part within the formula is the
Incrementer expression: ROW(A1)*20-20+5

Try putting the above expression (just add an equal sign in front) in any
starting cell, then copy down. You'll see that it returns: 5, 25, 45, ..
which is exactly the row number series that is wanted.

The part: ROW(A1)*20-20 gives us the required incremental steps of 20 as we
copy down, while the 5 is just a numerical adjustment since we want to start
with row 5.
[ Note that:=ROW(A1) in any cell returns 1, when we copy down it becomes
=ROW(A2) which returns 2, and so on.]

The friendly names expression within the HYPERLINK:

"Sheet2!B"&ROW(A1)*20-20+5

simply joins* the text: "Sheet2!B"
in front of the numbers: 5, 25, 45, ...
to produce: Sheet2!B5, Sheet2!B25, Sheet2!B45, ...
in the copy down

*i.e. concatenates

In the 2nd version, we used as the friendly names in the hyperlink, the
expression:

INDIRECT("'Sheet2'!B"&ROW(A1)*20-20+5))

Essentially we wrapped INDIRECT(...) around the earlier expression:
"Sheet2!B"&ROW(A1)*20-20+5

(Just regard the additional pair of apostrophes inserted before/after the
sheetname as a good practice to do when it comes to referencing sheetnames
within INDIRECT)

INDIRECT("'Sheet2'!B"&ROW(A1)*20-20+5))
would similarly evaluate to:

INDIRECT("Sheet2!B5"), INDIRECT("Sheet2!B25"), etc
as we copy down from the starting cell.

and INDIRECT(...) would then resolve all the textstrings
to return the actual contents of what's in:
Sheet2!B5, Sheet2!B25, Sheet2!B45, etc

(if the referenced cells were empty,
we'd simply get zeros returned)

As for HYPERLINK(...), and the ways that it functions with the pound sign
(#), CELL(...) and INDIRECT(...), please see Dave McRitchie's detailed
coverage at his:
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink
(scroll down to around mid-way on that page)

Hope the above helps ..

---
 
Back
Top