formulas for text
 

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,

formulas for text
 

Solution will depend on how you have organized your data...

Pl. provide more information so that one of us can help you.

"Suediff" wrote:

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,

formulas for text
 

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?

"Sheeloo" wrote:

Solution will depend on how you have organized your data...

Pl. provide more information so that one of us can help you.

"Suediff" wrote:

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,

formulas for text
 

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?

"Suediff" wrote:

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?

"Sheeloo" wrote:

Solution will depend on how you have organized your data...

Pl. provide more information so that one of us can help you.

"Suediff" wrote:

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,

formulas for text
 

Correct

"Sheeloo" wrote:

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?

"Suediff" wrote:

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?

"Sheeloo" wrote:

Solution will depend on how you have organized your data...

Pl. provide more information so that one of us can help you.

"Suediff" wrote:

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,

formulas for text
 

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").ClearConten ts

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

"Suediff" wrote:

Correct

"Sheeloo" wrote:

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?

"Suediff" wrote:

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?

"Sheeloo" wrote:

Solution will depend on how you have organized your data...

Pl. provide more information so that one of us can help you.

"Suediff" wrote:

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,

formulas for text
 

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!

"Sheeloo" wrote:

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").ClearConten ts

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

"Suediff" wrote:

Correct

"Sheeloo" wrote:

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?

"Suediff" wrote:

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?

"Sheeloo" wrote:

Solution will depend on how you have organized your data...

Pl. provide more information so that one of us can help you.

"Suediff" wrote:

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,

formulas for text
 

Sorry about that...
What you want can best be tackled by a macro..
Try to follow my instructions and see...

You may also see
http://www.taltech.com/support/sw_tricks/exmacros.htm

"Suediff" wrote:

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!