Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text join formulas working but act as straight text when modified | New Users to Excel | |||
if then formulas with text? | Excel Worksheet Functions | |||
formulas with text | New Users to Excel | |||
Text Formulas | Excel Discussion (Misc queries) | |||
text formulas | Excel Worksheet Functions |