Excel Formula for dynamic reporting

March 7, 2013 at 18:52:13
Specs: Windows 7
Hi-I'm trying to build a track roster - Tab1 will have Events going across and Names going down - a value (x) will be entered to indicate who's doing what. This will serve as a databse to be used weekly. Tab2 will be the coaches report and will follow the Event order for that week's Meet. Events will be running down and I want to show only the names running in events for the Meet - but this will change weekly and so needs to grow and shrink accordingly. I can do a lookup and sort the Events so I get them in the order for the meet but that's as far as it goes - Any suggestions would be greatly appreciated

See More: Excel Formula for dynamic reporting

Report •

March 8, 2013 at 17:08:51
Let me see if I understand this.

Sheet1 has this:

     A      B      C      D
1	   100M  1000M  5000M
2   Tom	    X		
3   Ed	          X      X
4   Bill    X            X
5   Dave                 X

In Sheet2, you want this:

     A      B    C     D
2   100M   Tom  Bill		
3   1000M  Ed
4   5000M  Ed   Bill  Dave

If that's correct, then try the following code.

Note: This code clears Sheet2 each time it is run. You may not want that, but since I can't see your workbook from where I'm sitting, I'm just offering this as a start. I would need a lot more details about your sheet(s) before any code specific to your layout could be written.

Sub TrackNames()
'Clear Sheet2 before each run
'Determine how many Events are listed in Sheet 1, Row 1
  srcEvents = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
'Determine how many Athletes are listed in Sheet 1, Row 1
  srcAthletes = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Place events in Sheet2, Column 1
  For dstEvents = 2 To srcEvents
   Sheets(2).Cells(dstEvents, 1) = Sheets(1).Cells(1, dstEvents)
'Loop through Events/names on Sheet1, copying Name when an X is found
  For events = 2 To srcEvents
   For athletes = 2 To srcAthletes
    If Sheets(1).Cells(athletes, events) = "X" Then
      nxtCell = Sheets(2).Cells(events, Columns.Count).End(xlToLeft).Column + 1
       Sheets(2).Cells(events, nxtCell) = Sheets(1).Cells(athletes, 1)
    End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question