Re: Moving rows with Hyperlink doesn't move hyperlink address

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, 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("address",OFFSET($A$100,$Z$1,1)),"#"&CELL("address",OFFSET($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("address",OFFSET($A$100,$Z$1,1)),"#"&CELL("address",OFFSET($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