Auto-Hyperlink

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"starguy" <starguy.24yb4a_1142829300.8817@excelforum-nospam.com> wrote in
message news:starguy.24yb4a_1142829300.8817@excelforum-nospam.com...
>
> 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
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=524175
>

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" wrote:

> 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
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "starguy" <starguy.24yb4a_1142829300.8817@excelforum-nospam.com> wrote in
> message news:starguy.24yb4a_1142829300.8817@excelforum-nospam.com...
> >
> > 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
> >
> >
> > --
> > starguy
> > ------------------------------------------------------------------------
> > starguy's Profile:
> http://www.excelforum.com/member.php...o&userid=32434
> > View this thread: http://www.excelforum.com/showthread...hreadid=524175
> >
>
>
>

"Starguy" wrote
> thanks Max it worked well

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

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....htm#hyperlink
(scroll down to around mid-way on that page)

Hope the above helps ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---