# 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

#1
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 1 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 Sheets(2).UsedRange.ClearContents '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) Next '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 Next Next End Sub ```Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions