Using a formula in a Hyperlink Cell reference
 

I have a workbook with approx 60 worksheets in it. I have set up hyperlinks
on the 1st sheet to take me directly to the worksheet I want to see. Is
there any way of using a function in the Hyperlink cell reference, so the
link will automatically position me at the right cell on the target sheet? I
thought I could use the COUNTA function to determine the first non-blank
cell, but the hyperlink doesn't seem to like it.

Hi
what HYPERLINK formula are you currently using?

--
Regards
Frank Kabel
Frankfurt, Germany

"sslabbe" schrieb im Newsbeitrag
...
I have a workbook with approx 60 worksheets in it. I have set up
hyperlinks
on the 1st sheet to take me directly to the worksheet I want to see.
Is
there any way of using a function in the Hyperlink cell reference, so
the
link will automatically position me at the right cell on the target
sheet? I
thought I could use the COUNTA function to determine the first
non-blank
cell, but the hyperlink doesn't seem to like it.

I've just started using the hyperlinks so everything is just set to go to
cell A1 now. What I want is to find the first blank cell in column J and
then position the cursor in the next row in column C. I thought I could use
the COUNTA function to identify the first blank cell, but I don't know how to
make the cell in column C active. I might just be entering the reference
incorrectly.

"Frank Kabel" wrote:

Hi
what HYPERLINK formula are you currently using?

--
Regards
Frank Kabel
Frankfurt, Germany

"sslabbe" schrieb im Newsbeitrag
...
I have a workbook with approx 60 worksheets in it. I have set up
hyperlinks
on the 1st sheet to take me directly to the worksheet I want to see.
Is
there any way of using a function in the Hyperlink cell reference, so
the
link will automatically position me at the right cell on the target
sheet? I
thought I could use the COUNTA function to determine the first
non-blank
cell, but the hyperlink doesn't seem to like it.

Hi
maybe you could also explain what you want to do afterwards (after
positioning the cursor)?

--
Regards
Frank Kabel
Frankfurt, Germany

"sslabbe" schrieb im Newsbeitrag
...
I've just started using the hyperlinks so everything is just set to
go to
cell A1 now. What I want is to find the first blank cell in column J
and
then position the cursor in the next row in column C. I thought I
could use
the COUNTA function to identify the first blank cell, but I don't
know how to
make the cell in column C active. I might just be entering the
reference
incorrectly.

"Frank Kabel" wrote:

Hi
what HYPERLINK formula are you currently using?

--
Regards
Frank Kabel
Frankfurt, Germany

"sslabbe" schrieb im
Newsbeitrag
...
I have a workbook with approx 60 worksheets in it. I have set up
hyperlinks
on the 1st sheet to take me directly to the worksheet I want to
see.
Is
there any way of using a function in the Hyperlink cell
reference, so
the
link will automatically position me at the right cell on the
target
sheet? I
thought I could use the COUNTA function to determine the first
non-blank
cell, but the hyperlink doesn't seem to like it.

If the first blank cell in column J is also after the last used cell in column J
and all of the rows above that have something in them (like a list that's
expanding downward), then this might work for you:

=HYPERLINK("#"&CELL("address",OFFSET(sheet1!C1,COU NTA(sheet1!J:J),0,1,1)),
"CLICKME")

(all one cell)

But if you have gaps in column J, then this won't work.

sslabbe wrote:

I've just started using the hyperlinks so everything is just set to go to
cell A1 now. What I want is to find the first blank cell in column J and
then position the cursor in the next row in column C. I thought I could use
the COUNTA function to identify the first blank cell, but I don't know how to
make the cell in column C active. I might just be entering the reference
incorrectly.

"Frank Kabel" wrote:

Hi
what HYPERLINK formula are you currently using?

--
Regards
Frank Kabel
Frankfurt, Germany

"sslabbe" schrieb im Newsbeitrag
...
I have a workbook with approx 60 worksheets in it. I have set up
hyperlinks
on the 1st sheet to take me directly to the worksheet I want to see.
Is
there any way of using a function in the Hyperlink cell reference, so
the
link will automatically position me at the right cell on the target
sheet? I
thought I could use the COUNTA function to determine the first
non-blank
cell, but the hyperlink doesn't seem to like it.



--

Dave Peterson