| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a cell value in its own calculation
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using a cell value in its own calculation
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using a cell value in its own calculation
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 On Jul 24, 8:18 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 ooglegroups.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- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using a cell value in its own calculation
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 wrote in message oups.com... 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 On Jul 24, 8:18 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 ooglegroups.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- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using a cell value in its own calculation
Perfect
Thanks alot man -Dave On Jul 24, 9:02 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 wrote in ooglegroups.com... 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 On Jul 24, 8:18 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 ooglegroups.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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using a cell value in its own calculation
I think what you want is conditional formatting
formatconditional 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 " wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| YTD calculation using same cell | Excel Worksheet Functions | |||
| Cell Calculation | Excel Worksheet Functions | |||
| How do I exclude a cell from an average calculation when the cell. | Excel Discussion (Misc queries) | |||
| format to calculation cell | Excel Discussion (Misc queries) | |||
| Calculation within a Cell | New Users to Excel | |||
Hybrid Mode
