Hyperlink to lowest text entry in table

[ddub25]

Hello

Can anyone help me with a Hyperlink formula that would take me to the lowest text entry in Column L in a table (the table is called "Table 2").

Dan

 

[Jerry Sullivan]

Hi Dan,

Try this:
=HYPERLINK(MID(CELL("filename"),SEARCH("[",CELL("filename")), 255) & "!" & ADDRESS(MATCH(9.99E+307,$L:$L),COLUMN($L$1)), "Display Text")

 

[T. Valko]

Hello

Can anyone help me with a Hyperlink formula that would take me to the lowest text entry in Column L in a table (the table is called "Table 2").

Dan

When you say "lowest text entry" do you mean the bottom-most (or LAST) text entry in the column?

 

[T. Valko]

When you say "lowest text entry" do you mean the bottom-most (or LAST) text entry in the column?

Is this what you had in mind:

Book1
	L	M
2	A	Last Text Entry
3	B	_
4	_	_
5	_	_
6	C	_
7	V	_
8	N	_
9	M	_
10	U	_
11	_	_
12	_	_
13	XX	_
14	_	_
15	_	_
16	_	_
17	_	_
18	_	_
19	_	_
20	_	_
Sheet1

This formula entered in M2:

=HYPERLINK("#L"&MATCH("zzzzz",L:L),"Last Text Entry")

When clicked, that hyperlink will take you to cell L13.

 

[Jerry Sullivan]

This formula entered in M2:

=HYPERLINK("#L"&MATCH("zzzzz",L:L),"Last Text Entry")

Biff, That is really slick! How does the # make "#L" evaluate differently that just "L" which doesn't work?

 

[ddub25]

Thanks very much for all your help. T.Valko I used yours, thanks again, very useful.

Dan

 

[T. Valko]

Biff, That is really slick! How does the # make "#L" evaluate differently that just "L" which doesn't work?

Thanks!

I'm not sure how that works. I've learned to use that by trial and error!

The # sign seems to tell the hyperlink that it must go to this position on this "page".

You may have seen the # sign used in URL hyperlinks. It seems to work the same way when used in Excel so this might be a "standard" use thing.

For example:

This URL hyperlink takes you to the entire page:

http://contextures.com/xlFunctions02.html

And this URL hyperlink with the # sign takes you to a specific location on that same page:

http://contextures.com/xlFunctions02.html#Trouble

 

[T. Valko]

Thanks very much for all your help. T.Valko I used yours, thanks again, very useful.

Dan

You're welcome. Thanks for the feedback!

 

[Jerry Sullivan]

The # sign seems to tell the hyperlink that it must go to this position on this "page".

You may have seen the # sign used in URL hyperlinks. It seems to work the same way when used in Excel so this might be a "standard" use thing.

For example:

This URL hyperlink takes you to the entire page:

http://contextures.com/xlFunctions02.html

And this URL hyperlink with the # sign takes you to a specific location on that same page:

http://contextures.com/xlFunctions02.html#Trouble

Biff, Thanks for the explanation. The URL Hyperlink is also very cool and helpful to know.
In the future, anytime I get stuck on a formula, I'll try placing a magic # in front to see if I get lucky!
Thanks again!

 

[T. Valko]

Biff, Thanks for the explanation. The URL Hyperlink is also very cool and helpful to know.
In the future, anytime I get stuck on a formula, I'll try placing a magic # in front to see if I get lucky!
Thanks again!

You're welcome!