Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problems
I ned help, I am trying to create a volleyball stat sheet in excel. The left
col lists the girls names and number, and across the top row is the list of stats we want to track. For example col. a is blocks I want to enter the girls number in the block cell and excel will then add 1 to that girls block col. and when I enter her number again excel will add 1 more so now she will have 2 blocks If ace=16,(b2+1)if ace=5,(b4+1) and so on. There are 8 girls and 7 stats if I enter that number again then excel will than add 1 so now the girl would have 2 ace Thanks for your help Ace Block Digs Kills Service Error Drops Return Er 16 21 5 9 AT 16 1 BG 5 1 JG 9 1 JL 8 MS 11 SC 23 SG 21 1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problems
I would not recommend trying to do what you want with formulas because with
iteration set to 1 I found that they updated the total whenever any block entry was made. There may be someone alone any minute to contradictthe above but I would do it with an Even Macro. With the Girls' names in A4:A11, the corresponding Girls' numbers in B4:B11, names of the Stats in C1:I1 and the number of the girl creating the stat in C2:I2 then the following Event Macro does what you want: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("B2:I2")) Is Nothing Then Exit Sub Dim Col As Integer Dim rRow As Long Dim x As Long Application.EnableEvents = False Col = Target.Column Range("C13:I13").ClearContents For x = 4 To 12 If Cells(x, 2).Value = Target.Value Then rRow = x Exit For End If Next x If x = 13 Then Beep Cells(13, Col).Value = "No girl of number " & Target.Value & " found" Target.Value = "" GoTo EndItAll End If Cells(x, Col).Value = Cells(x, Col).Value + 1 EndItAll: Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Lago2004" <u31105@uwe wrote in message news:6c934c0b52773@uwe... I ned help, I am trying to create a volleyball stat sheet in excel. The left col lists the girls names and number, and across the top row is the list of stats we want to track. For example col. a is blocks I want to enter the girls number in the block cell and excel will then add 1 to that girls block col. and when I enter her number again excel will add 1 more so now she will have 2 blocks If ace=16,(b2+1)if ace=5,(b4+1) and so on. There are 8 girls and 7 stats if I enter that number again then excel will than add 1 so now the girl would have 2 ace Thanks for your help Ace Block Digs Kills Service Error Drops Return Er 16 21 5 9 AT 16 1 BG 5 1 JG 9 1 JL 8 MS 11 SC 23 SG 21 1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problems
Sandy Mann wrote:
I would not recommend trying to do what you want with formulas because with iteration set to 1 I found that they updated the total whenever any block entry was made. There may be someone alone any minute to contradictthe above but I would do it with an Even Macro. With the Girls' names in A4:A11, the corresponding Girls' numbers in B4:B11, names of the Stats in C1:I1 and the number of the girl creating the stat in C2:I2 then the following Event Macro does what you want: Option Explicit Can you attach a file in excel so I can copy and paste into excel. I am very new to excel. My email is Thanks Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("B2:I2")) Is Nothing Then Exit Sub Dim Col As Integer Dim rRow As Long Dim x As Long Application.EnableEvents = False Col = Target.Column Range("C13:I13").ClearContents For x = 4 To 12 If Cells(x, 2).Value = Target.Value Then rRow = x Exit For End If Next x If x = 13 Then Beep Cells(13, Col).Value = "No girl of number " & Target.Value & " found" Target.Value = "" GoTo EndItAll End If Cells(x, Col).Value = Cells(x, Col).Value + 1 EndItAll: Application.EnableEvents = True End Sub I ned help, I am trying to create a volleyball stat sheet in excel. The left [quoted text clipped - 29 lines] SG 21 1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problems
Sandy
<snip There may be someone alone any minute to contradict the above but I would do it with an Even(sic) Macro. <more snip I don't think you'll get any argument in favor of using a cell as an accumulator cell. Following is my standard post on this subject. You can have a cumulative total in a cell if you have a separate source cell for adding a new total to the original. Use at your own risk. I am Posting this just to show you how it can be done, not as a good solution. You would be much better off to have another column so you can keep track of past entries. Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4) Enter this in cell D4 and then in ToolsOptionsCalculation check Iterations and set to 1. Now when you change the number in C4, D4 will accumulate. Note 1. If C4 is selected and a calculation takes place anywhere in the Application D4 will update even if no new number is entered in C4. NOT GOOD. Note 2. This operation is not recommended because you will have no "paper trail" to follow. Any mistake in entering a new number in C4 cannot be corrected. NOT GOOD. To clear out the accumulated total in D4 and start over, select D4 and EditEnter. Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL which does what you want without the re-calculation problem, but again there is no "paper trail" for back-checking in case of errors in data input. http://longre.free.fr/english/func_cats.htm Also see John McGimpsey's site for VBA method and the same caveats as above. http://www.mcgimpsey.com/excel/accumulator.html Gord Dibben Excel MVP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problems
Thank you for that Gord.
There may be someone alone any minute to contradict the above but I would do it with an Even(sic) Macro. that is because I type with two fingers and a tongue and I am dyslexic, (thus the *alone* instead of along), and so I don't *see* typos - unlike a secretary we had when I was working whom I swear could see a spelling mistake in a letter at ten paces! I have never used an accumulator but when I am in the company of all the clever people in these NG's I have learned never to say never. -- Rregards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk " |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problems
Sandy
If I'm going to blue-line your posts I should look a little closer<g It appears I don't see typos very well either. I also type with two fingers but my tongue is not long enough to reach the keyboard. Gord On Sat, 20 Jan 2007 22:38:59 -0000, "Sandy Mann" wrote: Thank you for that Gord. There may be someone alone any minute to contradict the above but I would do it with an Even(sic) Macro. that is because I type with two fingers and a tongue and I am dyslexic, (thus the *alone* instead of along), and so I don't *see* typos - unlike a secretary we had when I was working whom I swear could see a spelling mistake in a letter at ten paces! I have never used an accumulator but when I am in the company of all the clever people in these NG's I have learned never to say never. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problems
That will take a bit of work to use like you want and require coding in vba.
An easy alternative I came up with is to add a spin button from the toolbar, then in the sheet code add this, you click the cell with the players stat you want changed then click the button up or down. View-Toolbars-Visual Basic will get the toolbox (middle of the three) and it is one with up and down arrows, hover over and make sure it says spin button, click and then draw it where you want it, then double click it and insert the code. Private Sub SpinButton1_SpinDown() ActiveCell.Value = ActiveCell.Value - 1 End Sub Private Sub SpinButton1_SpinUp() ActiveCell.Value = ActiveCell.Value + 1 End Sub -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Lago2004" wrote: I ned help, I am trying to create a volleyball stat sheet in excel. The left col lists the girls names and number, and across the top row is the list of stats we want to track. For example col. a is blocks I want to enter the girls number in the block cell and excel will then add 1 to that girls block col. and when I enter her number again excel will add 1 more so now she will have 2 blocks If ace=16,(b2+1)if ace=5,(b4+1) and so on. There are 8 girls and 7 stats if I enter that number again then excel will than add 1 so now the girl would have 2 ace Thanks for your help Ace Block Digs Kills Service Error Drops Return Er 16 21 5 9 AT 16 1 BG 5 1 JG 9 1 JL 8 MS 11 SC 23 SG 21 1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problems | Excel Worksheet Functions |