Search higher cells function

Is it possible to write a function that approximately does this:

Creates a hyperlink that grabs a folder name from a cell in the same row. But IF the cell in that row is blank, it looks up the column until it hits a cell that actually has an entry, and CONCATENATE's it into a complete folder name.

Ie:


Cell A5 has a foldername where a specific file is kept. "Francis"
Cell B5 is where the function will be.

The function makes a hyperlink that concatenates a folder directory (lets say C:\database\somewhere\") and adds Francis\ (which is A1 + \) to its.

But.. this is where things get tricky. What if A5 was blank, I want the function to automatically look up (not all over, but strictly up) column A starting from A5 up until it hits a cell that isn't blank. From this point it would bring down the value of that cell into B5. and merge it together making the link:

C:\database\somwhere\Francis\filename.jpg

The filename.jpg would be programed into the function manually.



I hope this makes sense.

Hi

How about

Function myhyper(ra As Range)
  Dim strPath As String, strFile As String
  strPath = "C:\database\somewhere\"
  strFile = "\filename.jpg"
  cntr = 1
  If Not IsEmpty(ra) Then
    holder = ra.Value
  Else
    While IsEmpty(ra.Offset(cntr, 0))
      cntr = cntr + 1
    Wend
    holder = ra.Offset(cntr, 0)
  End If
  myhyper = strPath & holder & strFile
  
End Function

rylo