Rich -

I'm not smart enough on functions to solve your problem without a little bit
of code. But, the code is very simple. Here it is in case you want to use
it:

In Excel, hit Alt + F11 to get into the Visual Basic Editor.
Go to Insert, Module.
Paste this code into your new module:

Option Explicit
Public Function ReturnName(theCell As Range) As String

Dim irow As Long
Dim icol As Integer
Dim strText As String

If InStr(theCell.Value, "port") 0 Then 'if its a "port" header, skip it
ReturnName = ""
Else 'just a person's name, find the port above
irow = theCell.Row
icol = theCell.Column

'loop until you find a port name or the top of the sheet
Do Until irow = 1 Or InStr(Cells(irow, icol), ":") 0
irow = irow - 1
Loop

If irow = 1 Then 'top of sheet
ReturnName = "" 'return a blank
Else 'found a port name
strText = Cells(irow, icol).Value
'return port name
ReturnName = Right(strText, Len(strText) - InStr(strText, ":") -
1)
End If
End If
End Function

Now, in your worksheet, in cell B2, type the following formula:
=returnname(A2)

NOTE: This assumes your list of ports/names starts in cell A2. Copy it all
the way down and your problem should be solved.

One more note: I have experienced issues with Excel '07 recalculating these
custom functions when sheet changes occur, so just be aware of that.


"Rich Hayes" wrote:

Hi,

I have a query on some data i'm currently analysing and it's causing me a
headache. I'm hoping there is a simple solution to this that doesn't involve
macros.

Any help much appreciated

Sample data: this is what i have in one column of data at present. each name
beneath a port represents people working at that particular port. However,
what i want is in the column along side this data for it to show the port
name that each employee works at.

what i have at present is as follows;

cell a1 port:rotterdam
cell a2 richard
cell a3 david
cell a4 paul
cell a5 port:south africa
cell a6 james
cell a7 sam
cell a8 keith
cell a9 duncan
cell a10 port:port talbot
cell a11 simon
cell a12 rachel

what i'd like to see is a formula in column B to return the relevant port
for each individual (shown below) It is a large document with over 5000 rows
so a formula is a must if possible.

column A column B
Port: rotterdam
Richard rotterdam
David rotterdam
Paul rotterdam
Port: south africa
james south africa
sam south africa
keith south africa
duncan south africa
Port: port talbot
simon port talbot
rachel port talbot