Locating cell using Hyperlink

[stew]

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew

 

[stew]

The fist cell i want to look at is G23 then G65

 

[stew]

i HAVE NOW TRIED THIS AND IT WILL NOT OPEN ON THE CELL
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road
managers package\tour managers
spreadsheet.xls"&ADDRESS(6+42*ROW()-204,(COLUMN())-16)

this formula is in row 5

The fist cell i want to look at is G23 then G65

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew

 

[Sheeloo]

If you enter this in A1 and copy down
=(MOD(ROW(),42)-1)*42+23
then you will get 23,65, and so on...

You can concatenate this to get a string representing the cell address you
want
eg ="R"&((MOD(ROW(),42)-1)*42+23)&"C5" will give you R23C5
Put an INDIRECT around the above like this and you get the value from cell
R23C5 (on the same sheet)
=INDIRECT("R"&((MOD(ROW(),42)-1)*42+23)&"C5" ,False)

add the sheetname before "R" and you are home...

The fist cell i want to look at is G23 then G65

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew

 

[stew]

Hi sheeloo

I am trying to use the Hyperlink in my first post with and addition to the
end that will allow me to link to that work sheet at that cell. This addition
has to do what you have done but how do I include that in the in the
hyperlink.

Thanks Stew

i HAVE NOW TRIED THIS AND IT WILL NOT OPEN ON THE CELL
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road
managers package\tour managers
spreadsheet.xls"&ADDRESS(6+42*ROW()-204,(COLUMN())-16)

this formula is in row 5

The fist cell i want to look at is G23 then G65

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew

 

[Sheeloo]

You need to breakout your formulae into smaller chunks till they work. Then
you can combine them...

Assuming
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"
is taking you to A1 cell of the intended sheet
then enter this in A1
=LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road
managers package\tour managers spreadsheet.xls"
This should give you the complete path to the sheet you want
in B1 enter my formula
"R"&((MOD(ROW(),42)-1)*42+23)&"C5" to get the row/col reference in R1C1
format...
Now in C1 enter
=A1&"!"&B1 to get a complete string
in D1 enter
=Indirect(C1,False) to get the value in the target cell

If this works then put an Hyperlink around it in E1
=Hyperlink(Indirect(C1,False),"Descrption")

Once you get the hang of it then you can combine everything into one formula.

Hi sheeloo

I am trying to use the Hyperlink in my first post with and addition to the
end that will allow me to link to that work sheet at that cell. This addition
has to do what you have done but how do I include that in the in the
hyperlink.

Thanks Stew

i HAVE NOW TRIED THIS AND IT WILL NOT OPEN ON THE CELL
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road
managers package\tour managers
spreadsheet.xls"&ADDRESS(6+42*ROW()-204,(COLUMN())-16)

this formula is in row 5

The fist cell i want to look at is G23 then G65

:

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew

 

[stew]

Dear Sheeloo

I'm learing fast but you will have to give me some time with this one.

Wallllllllllllllllllllll

Thanks Sheeloo

You need to breakout your formulae into smaller chunks till they work. Then
you can combine them...

Assuming
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"
is taking you to A1 cell of the intended sheet
then enter this in A1
=LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road
managers package\tour managers spreadsheet.xls"
This should give you the complete path to the sheet you want
in B1 enter my formula
"R"&((MOD(ROW(),42)-1)*42+23)&"C5" to get the row/col reference in R1C1
format...
Now in C1 enter
=A1&"!"&B1 to get a complete string
in D1 enter
=Indirect(C1,False) to get the value in the target cell

If this works then put an Hyperlink around it in E1
=Hyperlink(Indirect(C1,False),"Descrption")

Once you get the hang of it then you can combine everything into one formula.

Hi sheeloo

I am trying to use the Hyperlink in my first post with and addition to the
end that will allow me to link to that work sheet at that cell. This addition
has to do what you have done but how do I include that in the in the
hyperlink.

Thanks Stew

i HAVE NOW TRIED THIS AND IT WILL NOT OPEN ON THE CELL
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road
managers package\tour managers
spreadsheet.xls"&ADDRESS(6+42*ROW()-204,(COLUMN())-16)

this formula is in row 5
:

The fist cell i want to look at is G23 then G65

:

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew

 

[ShaneDevenshire]

Hi,

With a dummy file I created a hyperlink via the Insert, Hyperlink command.
Then I modified it by adding B inside the quotes and &ROW(A5) outside.

HYPERLINK("Lesson12.xls!B"&ROW(A5))

See if you can start with these ideas and get something that works.

 

[stew]

Thanks shane. I can also see the shape of how to develop this. Thanks
stew

Hi,

With a dummy file I created a hyperlink via the Insert, Hyperlink command.
Then I modified it by adding B inside the quotes and &ROW(A5) outside.

HYPERLINK("Lesson12.xls!B"&ROW(A5))

See if you can start with these ideas and get something that works.

--
Thanks,
Shane Devenshire

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew

 

[stew]

Dear Shane / Sheeloo

I can Hyperlink to files outside my work book, by using insert hyperlink but
I cannot link to a worksheet within my book no matter how I try to do it. I
am using excel 2003 and Vista. Is there anything else I should be doing.
If I do not hear anything from anybody I will re post with a new thread

Thanks

Stewart

Thanks shane. I can also see the shape of how to develop this. Thanks
stew

Hi,

With a dummy file I created a hyperlink via the Insert, Hyperlink command.
Then I modified it by adding B inside the quotes and &ROW(A5) outside.

HYPERLINK("Lesson12.xls!B"&ROW(A5))

See if you can start with these ideas and get something that works.

--
Thanks,
Shane Devenshire

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew

 

[Dave Peterson]

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)

These formulas will adjust if you change the sheet name or insert/delete
rows/columns on the linked sheet.

Dear Shane / Sheeloo

I can Hyperlink to files outside my work book, by using insert hyperlink but
I cannot link to a worksheet within my book no matter how I try to do it. I
am using excel 2003 and Vista. Is there anything else I should be doing.
If I do not hear anything from anybody I will re post with a new thread

Thanks

Stewart

Thanks shane. I can also see the shape of how to develop this. Thanks
stew

Hi,

With a dummy file I created a hyperlink via the Insert, Hyperlink command.
Then I modified it by adding B inside the quotes and &ROW(A5) outside.

HYPERLINK("Lesson12.xls!B"&ROW(A5))

See if you can start with these ideas and get something that works.

--
Thanks,
Shane Devenshire


:

Hi

=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"

Gets me to tour managers spread sheet. What and how would I add to locate a
certain cell on that spread sheet using the row() ,5, that the above formula
is in. If I drag the cell down 1 I would like it to reflect a 42 row jump.
The data held in this cell repeats itself every 42 rows.

Thanks for looking

Stew