When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

Hi
the HYPERLINK always refers the cell position but not the content of this
cell. So you can't do it this way

"Samad" wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

Thanks Frank, but the problem is when I insert row I have to change all
hyperlink of the worksheet, can you suggest another way to link.


"Frank Kabel" wrote:

Hi
the HYPERLINK always refers the cell position but not the content of this
cell. So you can't do it this way

"Samad" wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

Hi
I'd guess you use the function 'Insert - Hyperlink' for your link. If you
could provide some more details what you exactly try to link there may be an
option to use the hYPERLINK formula instead.
Provide some example data for your linked sheet

"Samad" wrote:

Thanks Frank, but the problem is when I insert row I have to change all
hyperlink of the worksheet, can you suggest another way to link.


"Frank Kabel" wrote:

Hi
the HYPERLINK always refers the cell position but not the content of this
cell. So you can't do it this way

"Samad" wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

Ok Now I'm trying to give some example,
Sheet-A
Description Amount
------------ ----------
Loan to Staff 25,000
Advances to others 40,000

Sheet-B
Loan to Staff
---------------
Person-A 10,000
Person-B 15,000
---------
25,000
----------
Advances to others
----------------------
Item A 15,000
Item B 25,000
-------
40,000
---------
I want to hyperlink both descriptions in Sheet A to see detail, but when I
insert rows between the details of sheet B, it should be change hyperlink
address according to rows inserted so that I could insert new item.
but in result I have to change all hyperlink cell addresses.
I hope this example will clear the picture in your mind & I'll get your
valuable advice.
Thanks


"Frank Kabel" wrote:

Hi
I'd guess you use the function 'Insert - Hyperlink' for your link. If you
could provide some more details what you exactly try to link there may be an
option to use the hYPERLINK formula instead.
Provide some example data for your linked sheet

"Samad" wrote:

Thanks Frank, but the problem is when I insert row I have to change all
hyperlink of the worksheet, can you suggest another way to link.


"Frank Kabel" wrote:

Hi
the HYPERLINK always refers the cell position but not the content of this
cell. So you can't do it this way

"Samad" wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

http://support.microsoft.com/newsgro...heet.functions

"Samad" wrote:

Ok Now I'm trying to give some example,
Sheet-A
Description Amount
------------ ----------
Loan to Staff 25,000
Advances to others 40,000

Sheet-B
Loan to Staff
---------------
Person-A 10,000
Person-B 15,000
---------
25,000
----------
Advances to others
----------------------
Item A 15,000
Item B 25,000
-------
40,000
---------
I want to hyperlink both descriptions in Sheet A to see detail, but when I
insert rows between the details of sheet B, it should be change hyperlink
address according to rows inserted so that I could insert new item.
but in result I have to change all hyperlink cell addresses.
I hope this example will clear the picture in your mind & I'll get your
valuable advice.
Thanks


"Frank Kabel" wrote:

Hi
I'd guess you use the function 'Insert - Hyperlink' for your link. If you
could provide some more details what you exactly try to link there may be an
option to use the hYPERLINK formula instead.
Provide some example data for your linked sheet

"Samad" wrote:

Thanks Frank, but the problem is when I insert row I have to change all
hyperlink of the worksheet, can you suggest another way to link.


"Frank Kabel" wrote:

Hi
the HYPERLINK always refers the cell position but not the content of this
cell. So you can't do it this way

"Samad" wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

Hi
sorry, timed out session. This is the correct post:
-----

Hi
on sheet-A instead of the text Advances to others enter the following formula:
=HYPERLINK("#'sheet-B'!A" & MATCH("Advances to
others",'Sheet-B'!A1:A1000,0),"Advances to others")

and similar for Loan to Staff:
=HYPERLINK("#'sheet-B'!A" & MATCH("Loan to
Staff",'Sheet-B'!A1:A1000,0),"Loan to Staff")


"Frank Kabel" wrote:

http://support.microsoft.com/newsgro...heet.functions

"Samad" wrote:

Ok Now I'm trying to give some example,
Sheet-A
Description Amount
------------ ----------
Loan to Staff 25,000
Advances to others 40,000

Sheet-B
Loan to Staff
---------------
Person-A 10,000
Person-B 15,000
---------
25,000
----------
Advances to others
----------------------
Item A 15,000
Item B 25,000
-------
40,000
---------
I want to hyperlink both descriptions in Sheet A to see detail, but when I
insert rows between the details of sheet B, it should be change hyperlink
address according to rows inserted so that I could insert new item.
but in result I have to change all hyperlink cell addresses.
I hope this example will clear the picture in your mind & I'll get your
valuable advice.
Thanks


"Frank Kabel" wrote:

Hi
I'd guess you use the function 'Insert - Hyperlink' for your link. If you
could provide some more details what you exactly try to link there may be an
option to use the hYPERLINK formula instead.
Provide some example data for your linked sheet

"Samad" wrote:

Thanks Frank, but the problem is when I insert row I have to change all
hyperlink of the worksheet, can you suggest another way to link.


"Frank Kabel" wrote:

Hi
the HYPERLINK always refers the cell position but not the content of this
cell. So you can't do it this way

"Samad" wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

You could define a range name, then link to that.

Give Sheet1!C17 a nice range name.

Then you can link to that defined name (insert|hyperlink has an option for Place
in this document).

Or you could use a worksheet formula:
=HYPERLINK("#testname1")



Samad wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

--

Dave Peterson

Yes Dave, this is the simple way I also find it today from help thanks

"Dave Peterson" wrote:

You could define a range name, then link to that.

Give Sheet1!C17 a nice range name.

Then you can link to that defined name (insert|hyperlink has an option for Place
in this document).

Or you could use a worksheet formula:
=HYPERLINK("#testname1")



Samad wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

--

Dave Peterson

Hyperlinking to a range name works great until you sort the spreadsheet. Is
there any way to make range names that stay in tact when sorted?
--
Jim in MD


"Dave Peterson" wrote:

You could define a range name, then link to that.

Give Sheet1!C17 a nice range name.

Then you can link to that defined name (insert|hyperlink has an option for Place
in this document).

Or you could use a worksheet formula:
=HYPERLINK("#testname1")



Samad wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

--

Dave Peterson

Maybe put the name in an adjacent cell and then reapply the names after the
sort?

Jim wrote:

Hyperlinking to a range name works great until you sort the spreadsheet. Is
there any way to make range names that stay in tact when sorted?
--
Jim in MD

"Dave Peterson" wrote:

You could define a range name, then link to that.

Give Sheet1!C17 a nice range name.

Then you can link to that defined name (insert|hyperlink has an option for Place
in this document).

Or you could use a worksheet formula:
=HYPERLINK("#testname1")



Samad wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

--

Dave Peterson


--

Dave Peterson

Hi Frank

I have the same problem as Samad. I have tried to use your formula but I
get an #N/A error. I have substituted 'Sheet-B' for the name of my
worksheet.

My hyperlink example is:

Hyperlink "Initial consideration of bored piers" in the "Key Event Register"
worksheet to "Initial consideration of board piers" in the "Key Event
Document List" worksheet.

Any help would be greatly appreciated.

Dawn

"Frank Kabel" wrote:

Hi
sorry, timed out session. This is the correct post:
-----

Hi
on sheet-A instead of the text Advances to others enter the following formula:
=HYPERLINK("#'sheet-B'!A" & MATCH("Advances to
others",'Sheet-B'!A1:A1000,0),"Advances to others")

and similar for Loan to Staff:
=HYPERLINK("#'sheet-B'!A" & MATCH("Loan to
Staff",'Sheet-B'!A1:A1000,0),"Loan to Staff")


"Frank Kabel" wrote:

http://support.microsoft.com/newsgro...heet.functions

"Samad" wrote:

Ok Now I'm trying to give some example,
Sheet-A
Description Amount
------------ ----------
Loan to Staff 25,000
Advances to others 40,000

Sheet-B
Loan to Staff
---------------
Person-A 10,000
Person-B 15,000
---------
25,000
----------
Advances to others
----------------------
Item A 15,000
Item B 25,000
-------
40,000
---------
I want to hyperlink both descriptions in Sheet A to see detail, but when I
insert rows between the details of sheet B, it should be change hyperlink
address according to rows inserted so that I could insert new item.
but in result I have to change all hyperlink cell addresses.
I hope this example will clear the picture in your mind & I'll get your
valuable advice.
Thanks


"Frank Kabel" wrote:

Hi
I'd guess you use the function 'Insert - Hyperlink' for your link. If you
could provide some more details what you exactly try to link there may be an
option to use the hYPERLINK formula instead.
Provide some example data for your linked sheet

"Samad" wrote:

Thanks Frank, but the problem is when I insert row I have to change all
hyperlink of the worksheet, can you suggest another way to link.


"Frank Kabel" wrote:

Hi
the HYPERLINK always refers the cell position but not the content of this
cell. So you can't do it this way

"Samad" wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

Hi, if you are still there, think have found the answer. I combined a couple
of ideas that seem to work:

=HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)) ,"P2") for downward link
in document
=HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up



"Jim" wrote:

Hyperlinking to a range name works great until you sort the spreadsheet. Is
there any way to make range names that stay in tact when sorted?
--
Jim in MD


"Dave Peterson" wrote:

You could define a range name, then link to that.

Give Sheet1!C17 a nice range name.

Then you can link to that defined name (insert|hyperlink has an option for Place
in this document).

Or you could use a worksheet formula:
=HYPERLINK("#testname1")



Samad wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

--

Dave Peterson

Glad you found a solution that worked.

nastech wrote:

Hi, if you are still there, think have found the answer. I combined a couple
of ideas that seem to work:

=HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)) ,"P2") for downward link
in document
=HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up

"Jim" wrote:

Hyperlinking to a range name works great until you sort the spreadsheet. Is
there any way to make range names that stay in tact when sorted?
--
Jim in MD


"Dave Peterson" wrote:

You could define a range name, then link to that.

Give Sheet1!C17 a nice range name.

Then you can link to that defined name (insert|hyperlink has an option for Place
in this document).

Or you could use a worksheet formula:
=HYPERLINK("#testname1")



Samad wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

--

Dave Peterson


--

Dave Peterson

thanks, have been working on it alittle. Found somenting that might be good
for up or down (1 link - 1 button).

- item: (Mr. P) think I made a mistake on something in past, response
more personal nature, if you want to send blank email to nasgentech @ yahoo,
will reply. thanks

minor problem, just trying to cut # of hyperlinks using, in half (up vs. dn)

- item: would need to replace: row($A$20) with something more dynamic
(trying to get 1 button that works for up & down in a hyperlink).

This example works, but only if hyperlink is in the same line as: title
line / where working.
=HYPERLINK(IF(ROW($A$100)=ROW($A20),"#"&CELL("add ress",OFFSET($A$100,$Z$1,1)),"#"&CELL("address",OF FSET($A$100,-1,1))),"x")

Question: is there a way identify what "LINE" is currently in view / at
top of screen / below freeze pane, for modifying: row($a20)

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"Dave Peterson" wrote:

Glad you found a solution that worked.

nastech wrote:

Hi, if you are still there, think have found the answer. I combined a couple
of ideas that seem to work:

=HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)) ,"P2") for downward link
in document
=HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up

"Jim" wrote:

Hyperlinking to a range name works great until you sort the spreadsheet. Is
there any way to make range names that stay in tact when sorted?
--
Jim in MD


"Dave Peterson" wrote:

You could define a range name, then link to that.

Give Sheet1!C17 a nice range name.

Then you can link to that defined name (insert|hyperlink has an option for Place
in this document).

Or you could use a worksheet formula:
=HYPERLINK("#testname1")



Samad wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

--

Dave Peterson


--

Dave Peterson

I don't know a way of getting that first visible row under the freeze panes line
using worksheet functions.

And you can remove XSPAM from my address.

nastech wrote:

thanks, have been working on it alittle. Found somenting that might be good
for up or down (1 link - 1 button).

- item: (Mr. P) think I made a mistake on something in past, response
more personal nature, if you want to send blank email to nasgentech @ yahoo,
will reply. thanks

minor problem, just trying to cut # of hyperlinks using, in half (up vs. dn)

- item: would need to replace: row($A$20) with something more dynamic
(trying to get 1 button that works for up & down in a hyperlink).

This example works, but only if hyperlink is in the same line as: title
line / where working.
=HYPERLINK(IF(ROW($A$100)=ROW($A20),"#"&CELL("add ress",OFFSET($A$100,$Z$1,1)),"#"&CELL("address",OF FSET($A$100,-1,1))),"x")

Question: is there a way identify what "LINE" is currently in view / at
top of screen / below freeze pane, for modifying: row($a20)

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"Dave Peterson" wrote:

Glad you found a solution that worked.

nastech wrote:

Hi, if you are still there, think have found the answer. I combined a couple
of ideas that seem to work:

=HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)) ,"P2") for downward link
in document
=HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up

"Jim" wrote:

Hyperlinking to a range name works great until you sort the spreadsheet. Is
there any way to make range names that stay in tact when sorted?
--
Jim in MD


"Dave Peterson" wrote:

You could define a range name, then link to that.

Give Sheet1!C17 a nice range name.

Then you can link to that defined name (insert|hyperlink has an option for Place
in this document).

Or you could use a worksheet formula:
=HYPERLINK("#testname1")



Samad wrote:

When I Hyperlink cells to another sheet's cell and when I move data to other
row/ column or I insert rows/column it does not move hyperlinked address.

Can anyone advice me how to move hyperlink address with insert/move
rows/column/cells.
Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson