Find furthest right hyperlink

[SPACKlick]

Hi there, I have a register of items that sometimes get updated, the register is stored as

From row 9:how ever many hundreds of items we have

Columns A:E
Data about the item that does not change with new revisions

Column F
Current Revision

Columns G:Z
Revisions in order (ie if there had been 8 revisions
'-, A, B, C, D, E, F, G) (with higher revisions left blank)
each of which is hyperlinked to the relevant file for that revision.

What I would like is a forumla in Column F that will print 1) the letter of the highest revision (Revisions are always sequential)
2) Will hyperlink the column F to the correct revision...

I used to know how to do the first half of this formula but the spreadsheet got tinkered with and now I can't remember how to do it.

 

[SPACKlick]

EDIT: Found an old version of the document which is laid out very slightly differently, which has the following function in it "=IF(E9="","", IF(G9="", "/", INDIRECT(ADDRESS(ROW(F9), COUNTA(G9:Z9)+6,1,1))))"
Is this the best way to do it?

and still no hyperlinking.

 

[jasonb75]

With excel 2007 or newer

=IF(E9="","",IFERROR(LOOKUP("Z",G9:Z9),"/"))

Are you able to use VBA? I don't think you will be able to copy the link without using it.

 

[jasonb75]

If you can,

Function GetAddress(HyperlinkCell As Range)
    GetAddress = HyperlinkCell.Hyperlinks(1).Address
End Function

Then use the formula

=IF(E9="","",IF(G9="","/",HYPERLINK(GetAddress(INDEX(G9:Z9,COUNTA(G9:Z9))),LOOKUP("Z",G9:Z9))))