help with formula

[prich01]

I would like to create a spreadsheet that would help me automatically manage the required amount of rest for baseball pitchers. The columns of the spreadsheet would be the date and the rows would be the players. The requirements are as follows:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

1-25	no rest
26-55	1 day rest
56-75	2 days rest
76-119	3 days rest
120	4 days rest

<colgroup><col style="width: 100px"><col width="100"></colgroup><tbody>
</tbody>

So it would look something like this and would automatically determine required rest based upon the number of pitches entered in on any given date.


3/1/17 3/2/17 3/3/17 3/4/17 3/5/17 3/6/17
Smith 120 REST REST REST REST
Lewis 0 27 REST 58 REST REST
Brown 0 40 REST 35 REST

 

[Joe4]

Welcome to the Board!

Since your data grid contains both hard-coded values you are entering in (number of pitches) and automatically populated values (where REST appears), I think this is going to require VBA to do it, as any cell can only contain either a formula or a hard-coded value (but not both at the same time.

This VBA code is very dependent upon where all the data resides on the sheet. So please provide the details, i.e. What is the first row of data? What are the exact columns your dates are in?

 

[prich01]

So below is a link of what I have been working on. Something like this.

https://docs.google.com/spreadsheets/d/1wOM7K9h8aXSCfGhNdTOe4yWQKadmYQtuLpqiqSXtU1Q/edit?usp=sharing

 

[Joe4]

Right-click on the sheet tab name at the bottom of the sheet, select View Code, and enter this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated simultaneously
    If Target.Count > 1 Then Exit Sub

'   Only run if not in the first row or column
    If Target.Row = 1 Or Target.Column = 1 Then Exit Sub
    
    Application.EnableEvents = False
    
    Select Case Target.Value
        Case Is >= 120
            Range(Target.Offset(0, 1), Target.Offset(0, 4)) = "Rest"
        Case Is >= 76
            Range(Target.Offset(0, 1), Target.Offset(0, 3)) = "Rest"
        Case Is >= 56
            Range(Target.Offset(0, 1), Target.Offset(0, 2)) = "Rest"
        Case Is >= 16
            Target.Offset(0, 1) = "Rest"
        Case Is >= 1
            'Do nothing
        Case Else
            MsgBox "Invalid entry!"
    End Select

    Application.EnableEvents = True
    
End Sub

This should do what you want automatically. Just enter some values in and see what happens.

 

[prich01]

Wow that is awesome. One more question. Is there a way to get fancy and have the "REST" cells pop up with a red background?

 

[prich01]

I figured out the colors. If I type in 120 it automatically produces 4 consecutive days of rest. If I delete the 120 the other 4 cells don't automatically go back to blank. Is there a way for it to do that or is that asking too much?

 

[Eric W]

Just as an alternative, here is a formula solution that could work.

	A	B	C	D	E	F	G	H	I	J	K	L
1			3/13/2017	3/14/2017	3/15/2017	3/16/2017	3/17/2017	3/18/2017	3/19/2017	3/20/2017	3/21/2017	3/22/2017
2	Martin	0
3	Smith	0
4	Lewis	0
5	Thompson	0
6	Houston	0
7	Gates	0
8	R. Phillips	0
9	D. Phillips	0
10	Cole	0
11	Myers	0
12	Koinig	0
13	Davis	0
14	DeMars	0
15	Chesson	0
16	Ott	0
17
18	Pitches	Rest Days
19	0	0
20	25	1
21	55	2
22	75	3
23	120	4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula C2 =IF(COLUMN(C2)>VLOOKUP(LOOKUP(2,1/ISNUMBER($B2:B2),$B2:B2),$A$19:$B$23,2)+LOOKUP(2,1/ISNUMBER($B2:B2),COLUMN($B2:B2)),"","REST") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

At the start of the season, build your spreadsheet as shown, and put the C2 formula in as shown. Note the column of 0s in B, and note how I adjusted the Pitches/Rest Days table.

Now, whenever you have a number to enter, just put it in, overwriting the formula. It could look something like:

	A	B	C	D	E	F	G	H	I	J	K	L
1			3/13/2017	3/14/2017	3/15/2017	3/16/2017	3/17/2017	3/18/2017	3/19/2017	3/20/2017	3/21/2017	3/22/2017
2	Martin	0	24	57	REST	REST	77	REST	REST	REST
3	Smith	0
4	Lewis	0			56	REST	REST
5	Thompson	0						33	REST	27	REST
6	Houston	0
7	Gates	0
8	R. Phillips	0	88	REST	REST	REST	21
9	D. Phillips	0
10	Cole	0
11	Myers	0
12	Koinig	0
13	Davis	0
14	DeMars	0
15	Chesson	0
16	Ott	0
17
18	Pitches	Rest Days
19	0	0
20	25	1
21	55	2
22	75	3
23	120	4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

To get the red highlighting, whether you use Joe's method or mine, just use Conditional Formatting. Select columns B:ZZ (or whatever your last column is), click Conditional Formatting > Highlight Cells Rules > Text that Contains > type in REST and click OK.

 

[prich01]

I did just as you show and I get the following: A N/A in every cell and Did not find value '2' in LOOKUP evaluation.

 

[prich01]

nevermind, it does work in excel. Since I may be sharing with a large number of coaches, is there a way for this to work in google sheets?

 

[Eric W]

I'm sorry, I don't know enough about Google Sheets to say. You might try wrapping ArrayFormula() around it. And you do need to copy the formula to the entire range C2:L16.