Create link to last value in a list with variable entries

[Bptacek]

Using an array formula I can find the row number of the last entry in a list regardless of entries {=MAX(IF($A$10:$A$2000<>"",ROW($A$10:$A$2000)))}. The number of entries in list is never the same. While I can find the row number and display the value, I can't click to get to it. . I want to be able to create a link or name to get to that row but cannot find a away to create a link or name based on a variable that changes.

I thought INDIRECT might work but links and names can't be set based on a variable fields or formula. I searched forum but can't find a similar issue resolved, so hoping someone can give me some hints / suggestions on what to try.

Thank you in advance for any help

[sandy666]

Try:

Please Login or Register  to view this content.

The formula returns last value in column, numeric or text as hyperlink in the same sheet to the cell with values shown.
Returns value of highest row number in column A, using INDIRECT function for cell reference.

Enter as an array formula: type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.

Hope it will help

sandy


edit:
For more...

[FlameRetired]

For a Dynamic Named Range (FindNum) formula in Name Manager this:

Please Login or Register  to view this content.

Then drawing upon sandy666's formula this modified version. It does not require entering CSE.

Please Login or Register  to view this content.

For some strange reason it seems to execute faster and I don't know why.

[MarvinP]

Hi,

Another way to accomplish this is with a very short macro that looks like:

Please Login or Register  to view this content.

Now you can also put an Icon on the Quick Access Toolbar (QAT) that will fire the above macro and take you to the last entry in column A.

See the attached for the example.

[Bptacek]

I was able to use a combination of the recommendation to accomplish what I needed. I want to thank both FlameRetired and MarvinP for they great suggestions. I was not aware of the availability of the Hyperlink function, that will definitely come in handy in the future. So a huge thank you to both

[Bptacek]

HIt send to fast also wanted to include Sandy666 in my thank you

[sandy666]

If you got help - mark thread SOLVED! (Thread Tools->Mark thread as Solved) Thank You

[FlameRetired]

You're welcome. Thanks for marking this [SOLVED] , for the feedback and the rep.