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
>