Using a cell value in its own calculation

[silvz7105]

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

 

[Bernie Deitrick]

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

 

[Guest]

I think what you want is conditional formatting
format>conditional formating
change "cell value is" to "formula is" =left(L8,1)="A" format patern blue
add>>
condition 2
formula is =left(L8,1)="H" format pattern as red

 

[silvz7105]

Bernie, with some slight modification, this worked PERFECTLY.

Just 1 more question.

How can I code into the If/Else statement determining color, that if
you delete the entry, it make the color white??

I changed the IF structure to ->

If Left(Target.Value, 1) = "H" Then
Target.Interior.ColorIndex = 3
ElseIf Left(Target.Value, 1) = "h" Then
Target.Interior.ColorIndex = 3
ElseIf Left(Target.Value, 1) = "A" Then
Target.Interior.ColorIndex = 41
ElseIf Left(Target.Value, 1) = "a" Then
Target.Interior.ColorIndex = 41
Else: Target.Interior.ColorIndex = 2
End If

Thinking that if I went back and deleted the entry, it woudl change
the color to white, but it doesnt.

Any suggestions??


-Dave

 

[Bernie Deitrick]

Dave,

No need for the ElseIf .. then structure. Try the version below. You can change xlNone to 2 if you
really want the background to be white instead of the default color...

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 Target.Value = "" Then Target.Interior.ColorIndex = xlNone
If Len(Target.Value) <> 3 Then Exit Sub

On Error GoTo ErrHandler

Application.EnableEvents = False

If UCase(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

 

[silvz7105]

Perfect

Thanks alot man

-Dave

Dave,

No need for the ElseIf .. then structure. Try the version below. You can change xlNone to 2 if you
really want the background to be white instead of the default color...

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 Target.Value = "" Then Target.Interior.ColorIndex = xlNone
If Len(Target.Value) <> 3 Then Exit Sub

On Error GoTo ErrHandler

Application.EnableEvents = False

If UCase(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











- Show quoted text -