Hyperlink to last used text cell plus one on a different worksheet.

[Ben Verbeeten]

Hi,

I am trying to put a series of hyperlinks on worksheet #1 that acts as an index.

I want the hyperlink to jump to the next unused cell in column C on, for example, a worksheet called "COKE & COAL" The user can then put the next row of data in.

I have got as far as

=HYPERLINK("#COKE!"&ADDRESS(MATCH(REPT("z",255),COKE!C:C),3),LOOKUP(REPT("z",255),COKE!C:C))

Qn 1. How do I make it go plus one row down?

Qn 2. Can I use the insert hyperlink function and put the formula in the cell reference? This will enable me to label the hyperlink cell COKE & COAL.

Qn 3. Also found that the above formula does not allow for characters and spaces in the sheet name - thefore the truncation to COKE. Can I have COKE & COAL? Just easier to use COKE??

Thanks,


Ben.

[Ron Coderre]

Assuming the hyperlinked column contains text values...
try this regular formula:

=HYPERLINK("#'COKE & COAL'!"&ADDRESS(MATCH(REPT("z",255),'COKE & COAL'!C:C)+1,3),
LOOKUP(REPT("z",255),'COKE & COAL'!C:C))

Is that something you can work with?

[Ben Verbeeten]

Thanks Ron.

=HYPERLINK("#'COKE & COAL'!"&ADDRESS(MATCH(REPT("z",255),'COKE & COAL'!C:C)+1,3) works as I want it to. I can see the aspostphes on COKE & COAL and the +1. Figured it was +1 just not sure where.

Is the a way I can change the underlined 0 to a sensible label? Without macros or code etc. Some people at work dont like enabling macros on their PC.

When I cut and pasted the LOOKUP(REPT("z",255),'COKE & COAL'!C:C)) dropped into the next cell. And so it was not necessary.

[Ben Verbeeten]

Thought I could fix the label with cutom cell format to "COKE & COAL"

But once you click the link it goes to #'COKE & COAL'!$C$###

[Ben Verbeeten]

Hi Ron,

For some reason I couldn't get the friendly name to work. Works fine now!!! So with the friendly name tacked on the end the working formula is.......

=HYPERLINK("#'COKE & COAL'!"&ADDRESS(MATCH(REPT("z",255),'COKE & COAL'!C:C)+1,3),"COKE & COAL")

Solved. Looks good, link works.