In Document Hyperlink

Is there a way to mix use of HYPERLINK, and CELL functions, where reference
to CELL might move with line as document expands / contracts (where line
moves, in line positon). ABSOLUTE cell perhaps.

problem is that "in-document" hyperlink does not maintain integrity for
position.

=HYPERLINK(CELL("row",B469),"x") not working

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

nastech wrote:
>
> Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> to CELL might move with line as document expands / contracts (where line
> moves, in line positon). ABSOLUTE cell perhaps.
>
> problem is that "in-document" hyperlink does not maintain integrity for
> position.
>
> =HYPERLINK(CELL("row",B469),"x") not working

--

Dave Peterson

Thanks! been trying to figure that out for long time.. was told it could
not be done.
using: (where am allowed to name hyperlink with x.. been changing many
in-document links, many times daily, no more.

=HYPERLINK("#"&CELL("address",B1192),"x")

"Dave Peterson" wrote:

> David McRitchie posted this and it might help you:
>
> =HYPERLINK("#"&CELL("address",C5),C5)
> =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
> =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
>
> nastech wrote:
> >
> > Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> > to CELL might move with line as document expands / contracts (where line
> > moves, in line positon). ABSOLUTE cell perhaps.
> >
> > problem is that "in-document" hyperlink does not maintain integrity for
> > position.
> >
> > =HYPERLINK(CELL("row",B469),"x") not working
>
> --
>
> Dave Peterson
>

Hi, almost got to work, have to be close. I have been picking a line in my
document that might be 32 lines past the title line intended. is there a way
to add 32 rows/ lines within the formula? thanks

"Dave Peterson" wrote:

> David McRitchie posted this and it might help you:
>
> =HYPERLINK("#"&CELL("address",C5),C5)
> =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
> =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
>
> nastech wrote:
> >
> > Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> > to CELL might move with line as document expands / contracts (where line
> > moves, in line positon). ABSOLUTE cell perhaps.
> >
> > problem is that "in-document" hyperlink does not maintain integrity for
> > position.
> >
> > =HYPERLINK(CELL("row",B469),"x") not working
>
> --
>
> Dave Peterson
>

Why not just link the the cell you want?

(Add 32 rows to your address when you do the typing???)

nastech wrote:
>
> Hi, almost got to work, have to be close. I have been picking a line in my
> document that might be 32 lines past the title line intended. is there a way
> to add 32 rows/ lines within the formula? thanks
>
> "Dave Peterson" wrote:
>
> > David McRitchie posted this and it might help you:
> >
> > =HYPERLINK("#"&CELL("address",C5),C5)
> > =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
> > =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
> >
> > nastech wrote:
> > >
> > > Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> > > to CELL might move with line as document expands / contracts (where line
> > > moves, in line positon). ABSOLUTE cell perhaps.
> > >
> > > problem is that "in-document" hyperlink does not maintain integrity for
> > > position.
> > >
> > > =HYPERLINK(CELL("row",B469),"x") not working
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

hi, dont't know if I can explain.. 2 secs to show but, if you use in document
link to ?? line 900 (have been making link to say line 932), but header line
is at 900, now cut-paste line from line 901, to say line 800.

The header line has now moved to line 901. Next try doing that every couple
minutes, seconds in doing lots of work. move 5-10-50-more lines at once.
Header line is now at position ~ 1000. no longer can find reference to where
trying to do work.............

think answer would be some function, cell line row col offset, to fix header
line as the start position, +32.. feel certain it can be done. thanks



"Dave Peterson" wrote:

> Why not just link the the cell you want?
>
> (Add 32 rows to your address when you do the typing???)
>
> nastech wrote:
> >
> > Hi, almost got to work, have to be close. I have been picking a line in my
> > document that might be 32 lines past the title line intended. is there a way
> > to add 32 rows/ lines within the formula? thanks
> >
> > "Dave Peterson" wrote:
> >
> > > David McRitchie posted this and it might help you:
> > >
> > > =HYPERLINK("#"&CELL("address",C5),C5)
> > > =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
> > > =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
> > >
> > > nastech wrote:
> > > >
> > > > Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> > > > to CELL might move with line as document expands / contracts (where line
> > > > moves, in line positon). ABSOLUTE cell perhaps.
> > > >
> > > > problem is that "in-document" hyperlink does not maintain integrity for
> > > > position.
> > > >
> > > > =HYPERLINK(CELL("row",B469),"x") not working
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

ps. position plus 32.. might put header line at top of page..

"Dave Peterson" wrote:

> Why not just link the the cell you want?
>
> (Add 32 rows to your address when you do the typing???)
>
> nastech wrote:
> >
> > Hi, almost got to work, have to be close. I have been picking a line in my
> > document that might be 32 lines past the title line intended. is there a way
> > to add 32 rows/ lines within the formula? thanks
> >
> > "Dave Peterson" wrote:
> >
> > > David McRitchie posted this and it might help you:
> > >
> > > =HYPERLINK("#"&CELL("address",C5),C5)
> > > =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
> > > =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
> > >
> > > nastech wrote:
> > > >
> > > > Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> > > > to CELL might move with line as document expands / contracts (where line
> > > > moves, in line positon). ABSOLUTE cell perhaps.
> > > >
> > > > problem is that "in-document" hyperlink does not maintain integrity for
> > > > position.
> > > >
> > > > =HYPERLINK(CELL("row",B469),"x") not working
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

You have another suggestion at your other thread.

nastech wrote:
>
> ps. position plus 32.. might put header line at top of page..
>
> "Dave Peterson" wrote:
>
> > Why not just link the the cell you want?
> >
> > (Add 32 rows to your address when you do the typing???)
> >
> > nastech wrote:
> > >
> > > Hi, almost got to work, have to be close. I have been picking a line in my
> > > document that might be 32 lines past the title line intended. is there a way
> > > to add 32 rows/ lines within the formula? thanks
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > David McRitchie posted this and it might help you:
> > > >
> > > > =HYPERLINK("#"&CELL("address",C5),C5)
> > > > =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
> > > > =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
> > > >
> > > > nastech wrote:
> > > > >
> > > > > Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> > > > > to CELL might move with line as document expands / contracts (where line
> > > > > moves, in line positon). ABSOLUTE cell perhaps.
> > > > >
> > > > > problem is that "in-document" hyperlink does not maintain integrity for
> > > > > position.
> > > > >
> > > > > =HYPERLINK(CELL("row",B469),"x") not working
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

hi, you mean from another posting? thanks just read it. had just figured out
before that what answer might be, in combination with your/ other suggestion
similiar:

=HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)),"P2") for downwards
=HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up

not sure what the following is doing (or if all necessary)
=HYPERLINK("#"&CELL("address", not sure if I need the "address" part, or
exactly how this is working yet.. anyways year in making, might be solved.

"Dave Peterson" wrote:

> You have another suggestion at your other thread.
>
> nastech wrote:
> >
> > ps. position plus 32.. might put header line at top of page..
> >
> > "Dave Peterson" wrote:
> >
> > > Why not just link the the cell you want?
> > >
> > > (Add 32 rows to your address when you do the typing???)
> > >
> > > nastech wrote:
> > > >
> > > > Hi, almost got to work, have to be close. I have been picking a line in my
> > > > document that might be 32 lines past the title line intended. is there a way
> > > > to add 32 rows/ lines within the formula? thanks
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > David McRitchie posted this and it might help you:
> > > > >
> > > > > =HYPERLINK("#"&CELL("address",C5),C5)
> > > > > =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
> > > > > =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
> > > > >
> > > > > nastech wrote:
> > > > > >
> > > > > > Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> > > > > > to CELL might move with line as document expands / contracts (where line
> > > > > > moves, in line positon). ABSOLUTE cell perhaps.
> > > > > >
> > > > > > problem is that "in-document" hyperlink does not maintain integrity for
> > > > > > position.
> > > > > >
> > > > > > =HYPERLINK(CELL("row",B469),"x") not working
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

The # means that you're going to a subaddress. I bet you've seen that in links
to sites on the web.

This takes you to the top of one of Debra Dalgleish's pages:
http://contextures.com/xlFunctions03.html
This takes you to a location deeper in that page:
http://contextures.com/xlFunctions03.html#IndexMatch2



nastech wrote:
>
> hi, you mean from another posting? thanks just read it. had just figured out
> before that what answer might be, in combination with your/ other suggestion
> similiar:
>
> =HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)),"P2") for downwards
> =HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up
>
> not sure what the following is doing (or if all necessary)
> =HYPERLINK("#"&CELL("address", not sure if I need the "address" part, or
> exactly how this is working yet.. anyways year in making, might be solved.
>
> "Dave Peterson" wrote:
>
> > You have another suggestion at your other thread.
> >
> > nastech wrote:
> > >
> > > ps. position plus 32.. might put header line at top of page..
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Why not just link the the cell you want?
> > > >
> > > > (Add 32 rows to your address when you do the typing???)
> > > >
> > > > nastech wrote:
> > > > >
> > > > > Hi, almost got to work, have to be close. I have been picking a line in my
> > > > > document that might be 32 lines past the title line intended. is there a way
> > > > > to add 32 rows/ lines within the formula? thanks
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > David McRitchie posted this and it might help you:
> > > > > >
> > > > > > =HYPERLINK("#"&CELL("address",C5),C5)
> > > > > > =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
> > > > > > =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
> > > > > >
> > > > > > nastech wrote:
> > > > > > >
> > > > > > > Is there a way to mix use of HYPERLINK, and CELL functions, where reference
> > > > > > > to CELL might move with line as document expands / contracts (where line
> > > > > > > moves, in line positon). ABSOLUTE cell perhaps.
> > > > > > >
> > > > > > > problem is that "in-document" hyperlink does not maintain integrity for
> > > > > > > position.
> > > > > > >
> > > > > > > =HYPERLINK(CELL("row",B469),"x") not working
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks.. getting few items to check out for dynamic ranges etc.
Have others that were looking for answer such as this hyperlink, do you
think my example is complete, or any extraneous items.. or did I accidently
get it right ?

=HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)),"P2") for downwards
=HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up

for:
=HYPERLINK("#"&CELL("address",

Can you share why the second version didn't point at B504 directly instead of
using offset().



nastech wrote:
>
> Thanks.. getting few items to check out for dynamic ranges etc.
> Have others that were looking for answer such as this hyperlink, do you
> think my example is complete, or any extraneous items.. or did I accidently
> get it right ?
>
> =HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)),"P2") for downwards
> =HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up
>
> for:
> =HYPERLINK("#"&CELL("address",

--

Dave Peterson

sure: example is, in wanting my title line to be at top of page /view, I
would pick 32 lines (size of my view) past the title line. Else: if I pick
the title line as the view, title line can end up at bottom of screen (Really
need to see work items "below" title line, not above). should get rest? now
can pick title line, add new destination: 32 lines past.

Test: cut 50 lines from below your title line, paste above title line. go
to top of your excel document, click on hyperlink: title line is off the
page no longer seen (with old link). Can cut-paste all you want with OFFSET
(title-line is always the item referenced). re-negotiating page / view....
every 20 seconds was a pain...

this works, just don't know if have any extra items don't need
=HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)),"x") for downwards
=HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"x") for up

was told I do not need CELL("address",.. but could not get to work if
typing wrong:
=HYPERLINK("#"&OFFSET(A505,$Z$1,1),"x") was not working

was needing to pass this info along, was not sure if formula had extra stuff..



--------------------------

"Dave Peterson" wrote:

> Can you share why the second version didn't point at B504 directly instead of
> using offset().
>
>
>
> nastech wrote:
> >
> > Thanks.. getting few items to check out for dynamic ranges etc.
> > Have others that were looking for answer such as this hyperlink, do you
> > think my example is complete, or any extraneous items.. or did I accidently
> > get it right ?
> >
> > =HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)),"P2") for downwards
> > =HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up
> >
> > for:
> > =HYPERLINK("#"&CELL("address",
>
> --
>
> Dave Peterson
>

Then =Offset() makes sense since you're moving so much stuff around.

Hyperlinks travel to ranges, so you use =address().

If you look at what =offset() returns (the value from that cell--not the address
itself), you'll see why you want to use the address.

=HYPERLINK("#A1")
takes me to A1.

=hyperlink("#"&a1)
would only take me someplace if A1 contained something that looked like an
address.

nastech wrote:
>
> sure: example is, in wanting my title line to be at top of page /view, I
> would pick 32 lines (size of my view) past the title line. Else: if I pick
> the title line as the view, title line can end up at bottom of screen (Really
> need to see work items "below" title line, not above). should get rest? now
> can pick title line, add new destination: 32 lines past.
>
> Test: cut 50 lines from below your title line, paste above title line. go
> to top of your excel document, click on hyperlink: title line is off the
> page no longer seen (with old link). Can cut-paste all you want with OFFSET
> (title-line is always the item referenced). re-negotiating page / view....
> every 20 seconds was a pain...
>
> this works, just don't know if have any extra items don't need
> =HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)),"x") for downwards
> =HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"x") for up
>
> was told I do not need CELL("address",.. but could not get to work if
> typing wrong:
> =HYPERLINK("#"&OFFSET(A505,$Z$1,1),"x") was not working
>
> was needing to pass this info along, was not sure if formula had extra stuff..
>
> --------------------------
>
> "Dave Peterson" wrote:
>
> > Can you share why the second version didn't point at B504 directly instead of
> > using offset().
> >
> >
> >
> > nastech wrote:
> > >
> > > Thanks.. getting few items to check out for dynamic ranges etc.
> > > Have others that were looking for answer such as this hyperlink, do you
> > > think my example is complete, or any extraneous items.. or did I accidently
> > > get it right ?
> > >
> > > =HYPERLINK("#"&CELL("address",OFFSET(A505,$Z$1,1)),"P2") for downwards
> > > =HYPERLINK("#"&CELL("address",OFFSET(A505,-1,1)),"P2") for up
> > >
> > > for:
> > > =HYPERLINK("#"&CELL("address",
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson