Locating cell using Hyperlink

S

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
 
S

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
stew said:
The fist cell i want to look at is G23 then G65

stew said:
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
 
S

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...


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

stew said:
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
 
S

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

stew said:
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
stew said:
The fist cell i want to look at is G23 then G65

stew said:
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
 
S

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.
stew said:
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

stew said:
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
stew said:
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
 
S

stew

Dear Sheeloo

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

Wallllllllllllllllllllll

Thanks Sheeloo

Sheeloo said:
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.
stew said:
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

stew said:
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
 
S

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.
 
S

stew

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

ShaneDevenshire said:
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


stew said:
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
 
S

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

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

ShaneDevenshire said:
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


stew said:
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
 
D

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

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

ShaneDevenshire said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

display contents of cell in another workbook 7
File name question 0
File path 8
Copy and Paste Question 12
hyperlink 6
Hyperlink 13
ongoing hyperlink problem 3
Duplication Macro 0

Top