Using a formula in a Hyperlink Cell reference

[Guest]

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.

 

[Frank Kabel]

Hi
what HYPERLINK formula are you currently using?

 

[Guest]

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]

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

 

[Dave Peterson]

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,COUNTA(sheet1!J:J),0,1,1)),
"CLICKME")

(all one cell)

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