Dave,

You could use the worksheet's change event. Copy the code below, right-click the sheet tab, select
"View Code" and paste the code into the window that appears.

You need to have a table of team codes/names - name the two column table "TeamNames", with integers
in the first column, and the code will work on any string as you described, entered into a single
cell in column L.


HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Range("L:L"), Target) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
If Len(Target.Value) < 3 Then Exit Sub

On Error GoTo ErrHandler

Application.EnableEvents = False

If Left(Target.Value, 1) = "H" Then
Target.Interior.ColorIndex = 3
Else
Target.Interior.ColorIndex = 41
End If

Target.Value = Application.WorksheetFunction.VLookup( _
CInt(Right(Target.Value, 2)), Range("TeamNames"), 2, False)

ErrHandler:

Application.EnableEvents = True

End Sub


wrote in message oups.com...
Im not sure if this can be done in a single cell formula, or if I'd
have to delve into the VB side of Excel.

I am wrting a spreadsheet to use in a league, where ti maintains
schedules, calculates points system, rankings, etc. I am trying to
make the scheduling easier, and this is where I am running into a
problem.

I would like to take the value of a given cell, in this case L8, and
use it in a vertical lookup function, where the function would output
back into cell L8.

I have all of teams numbered sequencially (01, 02, 03, 04 - 60). In
Cell L8, I would like to be able to type 3 characters. the first
would be either an H or an A. H being home, A being away. If
right(L8,1)=H then cell color=red. If right(L8,1)=A then cell
color=blue. The second 2 characters will be a team number. Such that
if I type in A05, the cell color will turn Blue, and the name of Team
5 will appear in said cell.

I would very much like tobe able to do this with 1 cell, no hidden
columns or anythng like that. Im not sure if a circular reference
like this can work without the VB code side of Excel.

Please advise

-Dave