This one has got me stumped!

A colleague has created a spreadsheet with a series of columns of text
data to indicate the responsible manager's) for a particular item.
There are 7 columns, each column unique to one of the seven managers.
An item (think row of data) can have 1 to 7 managers responsible. All
ok so far, but the 'lead' manager for the item has been indicated by
underlining the text name in one of the seven columns.

My task is to create one column which picks just the lead manager
(underlined name) from the possible list of seven.

The sample data would look something like this:

     A       B       C       D       E       F       G       H
1  *Alice*
2           *Bert*          Dave    Elise
3  *Alice*          Cloe                          Geoff
4                   Cloe                  *Fred*

etc. Where the *name* indicates underlined text in the original spreadsheet.

The output that I want in column H, say, is:

     H
1   Alice
2   Bert
3   Alice
4   Fred

I'm familiar with basic VBA and macros and I think the solution might
be heading down that route. I did a quick G**gle search and found some
pages on conditional format style searches and searches by colour of
the cell, but nothing on the cell font attribute.

Apologies for the meagre offering, but I'm hoping the intellectual
challenge may prove irresistible!

You could create a new macro as such:

================================
Public Function GetUnderlinedCell(CellRef As Range) As String
Dim c As Integer
Dim sResult As String

   'Force Running when Recalculating Since Formatting Only
   Application.Volatile True
   
   'Assume Unknown
   sResult = "Unknown"
   
   'Loop Thru Each Column and Test for Underline
   For c = 1 To CellRef.Columns.Count
      If CellRef.Columns(c).Font.Underline = xlUnderlineStyleSingle Then
         sResult = CellRef.Columns(c).Value
      End If
   Next c
      
   'Return Results
   GetUnderlinedCell = sResult
End Function
================================

Then in the cells that you want to return the manager use the formula
"=GetUnderlinedCell(A1:G1)" changing the cell range accordingly.

You will need to press F9 to recalc the sheet when done, or when you
make changes to the formatting of the data.

-Stolis

thanks Stolis, that has done the trick. Let me know what you want me
to do with the $5 that would have been yours if you'd been a
researcher!

I think the other lesson learnt for me is to try and encourage my
colleague to create 'standard' excel spreadsheets in future!

f

My comments are free.  Like you said, the "intellectual challenge" was
irresistible.  Feel free to donate the $5 to charity or such.  Glad I
was able to help.

-Stolis