Macro to read another cells hyperlink address

Hello,

In my workbook on the front tab 'Draw Request' people will be entering a 4 digit number in cells B13-B38. When they do I would like the corresponding column Q cell to return either OK or NO if the 4 digit number can be found in column K on my back tab titled 'Photo Tracking'.

If it returns OK I would like to be able to click OK and have it open the same hyperlink that is in column C of the same row as the 4 digit number on 'Photo 'Tracking. (I.E. I enter the number 0740 in 'Draw Request' B13, column Q returns OK because on 'Photo Tracking' the number 0740 is found in K5 and when I click the OK it opens the same hyperlink that is in 'Photo Tracking' C5.)

Currently I am using this formula
=IF(ISBLANK(B13),"",IF(ISNA(VLOOKUP("*"B13&"*",'Photo Tracking'!K5:K93,1,FALSE)),"NO",HYPERLINK(INDEX('Photo Tracking'!C:C,MATCH("*"&'Draw Request'!B13&"*",'Photo Tracking'!K:K,0)),"OK")))

Now, when I go to the 'Photo Tracking tab' and right-click cell C5 to hyperlink to a document titled "1 Loretta.xlsx" the equation works perfectly. I can type 0740 into cell B13 on 'Draw Request', Q13 will return OK and when I click OK, 1 Loretta will open up.

However, in the 'Photo Tracking' tab I don't want to display 1 Loretta.xlsx in cell C5, I would like to display the number 4. When I type 4 and click cell C5 the hyperlink still works, but when I return to the 'Draw Request' tab and click the OK in cell Q13, I get the error message "Cannot open the specified file".

I know I need to use a macro to tell the formula to read another cell's hyperlink target address, unfortunately I have no idea how to do this.

Any help provided would be greatly appreciated.

Sample Workbook.xlsx

I hope this worked and is enough data.

The hyperlinks wont work but they are the numbers 3 in the Week column on 'Photo Tracking'.

Thanks for the assistance.

I have actually figured this out and have a working macro.

Now I am wondering how I can get the formula to read from the bottom-top, rather than top-bottom.

I would like for it to start at the bottom of column K on 'Photo Tracking' and go up, but I'm not exactly sure how to do that.

Xladept,

Thank you for your assistance. The current formula I'm using is:
=IF(ISBLANK(B13),"",IF(ISNUMBER(MATCH("*"&'Draw Request'!B13&"*",'Photo Tracking'!K:K,0)),HYPERLINK(gethyperlinkaddress(INDEX('Photo Tracking'!C:C,MATCH("*"&'Draw Request'!B13&"*",'Photo Tracking'!K:K,0))),"OK"),"NO"))

gethyperlinkaddress is the macro I have created. I am looking for a way to have it search column K of 'Photo Tracking' from newest entry to latest.

Any help would be greatly appreciated.

Sixthsense,

Thank you for your assistance. That formula worked perfectly.