software

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 amp; formulas.
suppose i have hyperlink in cell quot;Sheet1!A5quot; linked to quot;Sheet2!B5quot;, and
in quot;Sheet1!A6quot; linked to quot;Sheet2!B25quot;. i want that when cell quot;Sheet1!A6quot;
copied down it should automatically create hyperlink with cell
quot;Sheet2!B45quot;.
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: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=524175One play ..

Put in say, C5:

=HYPERLINK(quot;#quot;amp;CELL(quot;addressquot;,
INDIRECT(quot;'Sheet2'!Bquot;amp;ROW(A1)*20-20 5)),quot;Sheet2!Bquot;amp;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(quot;#quot;amp;CELL(quot;addressquot;,
INDIRECT(quot;'Sheet2'!Bquot;amp;ROW(A1)*20-20 5)),INDIRECT(quot;'Sheet2'!Bquot;amp;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
savefile.com/projects/236895
xdemechanik
---
quot;starguyquot; gt; wrote in
message ...
gt;
gt; i have a problem which seems me very difficult to solve. i have not read
gt; about that on internet.
gt; infact i want to make hyperlink in such a way that when cell whick has
gt; link to other cell copied down the cell it linked to would also be
gt; changed as we observe in coping other functions amp; formulas.
gt; suppose i have hyperlink in cell quot;Sheet1!A5quot; linked to quot;Sheet2!B5quot;, and
gt; in quot;Sheet1!A6quot; linked to quot;Sheet2!B25quot;. i want that when cell quot;Sheet1!A6quot;
gt; copied down it should automatically create hyperlink with cell
gt; quot;Sheet2!B45quot;.
gt; i want to have this without using VBA.
gt; please tell me if it could be done.
gt;
gt; waiting for a solution
gt; regards
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=524175
gt;
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.

quot;Maxquot; wrote:

gt; One play ..
gt;
gt; Put in say, C5:
gt;
gt; =HYPERLINK(quot;#quot;amp;CELL(quot;addressquot;,
gt; INDIRECT(quot;'Sheet2'!Bquot;amp;ROW(A1)*20-20 5)),quot;Sheet2!Bquot;amp;ROW(A1)*20-20 5)
gt;
gt; Copy C5 down
gt;
gt; The above will insert friendly names into C5 down, viz.:
gt;
gt; In C5: Sheet2!B5
gt; In C6: Sheet2!B25
gt; In C7: Sheet2!B45
gt; and so on
gt;
gt; and hyperlink the cells direct to the destinations
gt; (clicking on C5 will bring you to Sheet2!B5, C6 goes to Sheet2!B25, etc)
gt;
gt; ----
gt; And if we want to make the actual contents of the destinations
gt; show up as the friendly names in the hyperlinked cells,
gt; we could try this instead in C5:
gt;
gt; =HYPERLINK(quot;#quot;amp;CELL(quot;addressquot;,
gt; INDIRECT(quot;'Sheet2'!Bquot;amp;ROW(A1)*20-20 5)),INDIRECT(quot;'Sheet2'!Bquot;amp;ROW(A1)*20-20
gt; 5))
gt;
gt; and copy down as before
gt;
gt; So if
gt;
gt; Sheet2!B5 contains: 100
gt; Sheet2!B25 contains: 200
gt; Sheet2!B45 contains: 300
gt;
gt; what we'll see is,
gt;
gt; In C5: 100
gt; In C6: 200
gt; In C7: 300
gt;
gt; with the hyperlinks continuing to function as before
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;starguyquot; gt; wrote in
gt; message ...
gt; gt;
gt; gt; i have a problem which seems me very difficult to solve. i have not read
gt; gt; about that on internet.
gt; gt; infact i want to make hyperlink in such a way that when cell whick has
gt; gt; link to other cell copied down the cell it linked to would also be
gt; gt; changed as we observe in coping other functions amp; formulas.
gt; gt; suppose i have hyperlink in cell quot;Sheet1!A5quot; linked to quot;Sheet2!B5quot;, and
gt; gt; in quot;Sheet1!A6quot; linked to quot;Sheet2!B25quot;. i want that when cell quot;Sheet1!A6quot;
gt; gt; copied down it should automatically create hyperlink with cell
gt; gt; quot;Sheet2!B45quot;.
gt; gt; i want to have this without using VBA.
gt; gt; please tell me if it could be done.
gt; gt;
gt; gt; waiting for a solution
gt; gt; regards
gt; gt;
gt; gt;
gt; gt; --
gt; gt; starguy
gt; gt; ------------------------------------------------------------------------
gt; gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=524175
gt; gt;
gt;
gt;
gt;

quot;Starguyquot; wrote
gt; thanks Max it worked well

You're welcome, Starguy !

gt; but this formula is difficult to understand for me.
gt; i'll try to understand it so that i can create it by myself.
gt; 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:

quot;Sheet2!Bquot;amp;ROW(A1)*20-20 5

simply joins* the text: quot;Sheet2!Bquot;
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(quot;'Sheet2'!Bquot;amp;ROW(A1)*20-20 5))

Essentially we wrapped INDIRECT(...) around the earlier expression:
quot;Sheet2!Bquot;amp;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(quot;'Sheet2'!Bquot;amp;ROW(A1)*20-20 5))
would similarly evaluate to:

INDIRECT(quot;Sheet2!B5quot;), INDIRECT(quot;Sheet2!B25quot;), 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:
www.mvps.org/dmcritchie/excel....htm#hyperlink
(scroll down to around mid-way on that page)

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