formulas for text

  • Thread starter Thread starter Suediff
  • Start date Start date
S

Suediff

I assign field hockey and lacrosse games. After all assignments are made I
would like to be able to see all the games that the individual refs have. I
know I can use Find and Select to see if I have double booked them but I want
to be able to print a list of their games also for payment purposes. Is there
a way I can do that? Thank you,
 
Solution will depend on how you have organized your data...

Pl. provide more information so that one of us can help you.
 
It is listed in columns by "fields" and in rows by "time". so Sue might have
a game at 8AM, 9AM on Field 1 and at 10, and 11 on field 3 and 12, and 1 on
field 8. And sometimes there are so many "fields" that I have to use 3
sheets, that is why I would like to be able to "total" the number of games
they have. Is that what you meant?
 
That helps...

So you have time slots in Row 1 (in B2,C2,... upto?
Names in Col A?

Fields for the name in that row in Col B, C etc for that row?
 
Correct

Sheeloo said:
That helps...

So you have time slots in Row 1 (in B2,C2,... upto?
Names in Col A?

Fields for the name in that row in Col B, C etc for that row?
Click to expand...
 
Assuming your data is in Sheet1..
Enter the coach for whom you want to generate the report in B1 of Sheet2.

The macro will list the games for that coach in Sheet2

To run the macro
Press ALT-F11 to open VB Editor
Choose Insert|Module
Paste the code below in the module
Press F5
Click OK

Here is the macro...

Sub copyMacro()
Dim lastRow1, lastRow2 As Long
Dim lastCol As Long
Dim i, j, k, startRow As Long
Dim coachName As String
Dim timeSlot(255) As String

With Worksheets("Sheet1")
lastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With Worksheets("Sheet1")
lastCol1 = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

For i = 2 To lastCol1
timeSlot(i) = Worksheets("Sheet1").Cells(1, i)
Next i
Worksheets("Sheet2").Range("A2:T2000").ClearContents

coachName = Worksheets("Sheet2").Range("B1").Value
j = 2
Worksheets("Sheet2").Cells(j, 1) = "Time Slot"
Worksheets("Sheet2").Cells(j, 2) = "Field Name"
j = j + 1
For i = 2 To lastRow1
If Worksheets("Sheet1").Cells(i, 1) = coachName Then
For k = 2 To lastCol1
fieldname = Worksheets("Sheet1").Cells(i, k)
If fieldname <> "" Then
Worksheets("Sheet2").Cells(j, 1) = timeSlot(k)
Worksheets("Sheet2").Cells(j, 2) = fieldname
j = j + 1
End If
Next k
End If
Next i

MsgBox "Processing Complete"
End Sub
 
Sorry but that makes no sense to me at all. That's why I posted in "new
users". If that's what has to be done than I guess I can't do it. I have no
idea what "macro" is!
 
Back
Top