Hyperlink

[MrDoc]

Hi,

I'd like to create an hyperlink to the first blank (empty) row in column A of a sheet. Is it possible to use "Insert / Hyperlink" and input a formula in the "Type the cell reference" box?

If so, which formula? If not, is there another way (not using VBA), so that I can type in the hyperlink cell "Go to first blank row in column A" and actually go there by clicking in the cell?

Thanks for any help.

MrDoc

 

[jbeaucaire]

Put this formula in a cell and it will do that...same sheet:

=HYPERLINK("#" & ADDRESS(MATCH("zzz",A:A, 1)+1, 1), "Go to first empty cell in column A")


....different sheet:

=HYPERLINK("#Sheet1!" & ADDRESS(MATCH("zzz",Sheet1!A:A, 1)+1, 1), "Go to first empty cell in Sheet1 column A")

 

[MrDoc]

Thank you for replying, Jean.

However, the result of your formula takes the cursor to A5, which is not an empty cell.

I should have mentioned that I really want to hyperlink to the first blank cell in range A5:A155.

I made some experiences with your formula, replaced commas (here we must use semicolons) and tried in column B. The formula I used is:

=HYPERLINK("#" & ADDRESS(MATCH("zzz";B:B; 1)+1; 1); "Go to first empty cell in column A")

Using it, I really hyperlinked to the first empty cell in column A... but I have no idea why!

Column A contains numbers (from A5 to A155), column B contains names, also from A5 to A155).

Can you please explain the formula and find out why the changes I tried made it work?

Best Regards,
MrDoc

 

[MrDoc]

After considering the formula, I came to the conclusion that the problem lied in the "zzz" parameter in the match function, since column A has numbers, not text. So I changed "zzz" to 9999999, kept A:A as range, and it works fine. Anyway, it also worked with range (B:B), as I said in my last message, because the hyperlink is made to the first empty column in the first row, so A.

Thanks a lot!

 

[jbeaucaire]

Glad you got it, and by deciphering that, you probably have a much better understanding of the technique... so let's pretend I did that on purpose.

Go team.

 

[MrDoc]

Thanks again, Jean