Copying and Filtering Data and keeping new sheet updated.

[Andy Donegan]

Hi, Iam working in Excel 2010 and would really appreciate some advice going forward on the next stage of my current project for our swimming club.

Everything currently within the spreadsheet works via formula's I have no coding experience as of yet, I am starting to realise this is my next step probably after searching for some time for the answer to my next step.

I have a table BSC-All Meets - this hold a record of every single swim every swimmer for the club ever attends and holds there swim times.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]BSC all Swimmers[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Event[/TD]
[TD]Course[/TD]
[TD]Distance[/TD]
[TD]Time[/TD]
[TD]Place[/TD]
[TD]Outof[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]K&D 2013[/TD]
[TD]CharlieSmith[/TD]
[TD]14/04/2013[/TD]
[TD]FreeStyle[/TD]
[TD]short[/TD]
[TD]50m[/TD]
[TD]0:41.25[/TD]
[TD]6[/TD]
[TD]30[/TD]
[TD]Heat[/TD]
[/TR]
</tbody>[/TABLE]

Table Shourt Course - PB's then filters the data and is the main screen for showing all of the swimmers and there current personal best times this updates from BSC-All Meets.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Swimmer Name[/TD]
[TD]Sex[/TD]
[TD]DOB[/TD]
[TD]AOD[/TD]
[TD]Squad[/TD]
[TD]COBSC[/TD]
[TD]Active[/TD]
[TD]25
Back[/TD]
[TD]25
Breast[/TD]
[TD]25
Free[/TD]
[TD]25
Fly
[/TD]
[TD]50
Back[/TD]
[TD]50
Breast[/TD]
[TD]50
Free[/TD]
[TD]50
Fly[/TD]
[TD]100
Back[/TD]
[TD]100
Breast[/TD]
[TD]100
Free[/TD]
[TD]100
Fly[/TD]
[TD]100
IM[/TD]
[TD]200
Back[/TD]
[TD]200
Breast[/TD]
[TD]200
Free[/TD]
[TD]200
Fly[/TD]
[TD]200
IM[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Charlie Smith[/TD]
[TD]M[/TD]
[TD]31/01/2013[/TD]
[TD]10[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]0:22.34[/TD]
[TD]0:24.45[/TD]
[TD]0:18.25[/TD]
[TD]0:25.34[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now my next project on this, we enter many different gala series through out the year with different criteria for each race.

Gala 3 - 28th September
Age of Competitors as of 16/11/2013
[TABLE="width: 185"]
<tbody>[TR]
[TD="colspan: 2"]I. Boys 9/10yrs 2LFreestyle[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 256"]
<tbody>[TR]
[TD="colspan: 3"]2. Girls 9/10yrs 2L Freestyle[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]3. Boys 15yrs/Over 4L Breaststroke[/TD]
[/TR]
[TR]
[TD="colspan: 4"]4. Girls 15yrs/Over 4L Breaststroke[/TD]
[/TR]
[TR]
[TD="colspan: 4"]S. Boys 11/12yrs 4xI Individual Medley[/TD]
[/TR]
[TR]
[TD="colspan: 4"]6. Girls 11/12yrs 4xl Individual Medley[/TD]
[/TR]
[TR]
[TD="colspan: 3"]7. Boys 13/14yrs 4L Backstroke[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]8. Girls 13/14yrs 4L Backstroke[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]9. Men's Open 2L Butterfly[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]10. Ladies' Open 2L Butterfly[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]11. Boys 9/10yrs 4x1 Freestyle Team[/TD]
[/TR]
[TR]
[TD="colspan: 4"]12. Girls 9/10yrs 4x1 Freestyle Team[/TD]
[/TR]
[TR]
[TD="colspan: 4"]13. Boys l5yrs/Over4x2 Medley Team[/TD]
[/TR]
[TR]
[TD="colspan: 4"]14. Girls l5yrs/Over 4x2 Medley Team[/TD]
[/TR]
</tbody>[/TABLE]

Now i have achieved something basic to what I would like i.e. I have managed to use auto filter to pull through All the boys aged 9 to 10 and put them on to a new sheet. The problem now is that data becomes static and will not update as the year progresses.

A lot of parents enter there children for the galas early in the year, but it will help us coaches greatly if we can attend with a sheet showing which swimmers have signed up or are elligeble to swim for events etc and with there current best times.

I am looking to create a new sheet with a single field at the top Age on date which would be entered by the coach for the gala series for the year.

Then each event would be listed like below

1. Boys 9/10yrs 2l Freestyle

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SwimmerName[/TD]
[TD]Sex[/TD]
[TD]DOB[/TD]
[TD]AOD[/TD]
[TD]Squad[/TD]
[TD]COBSC[/TD]
[TD]Active[/TD]
[TD]25
Back[/TD]
[TD]25
Breast[/TD]
[TD]25
Free[/TD]
[TD]25
Fly
[/TD]
[TD]50
Back[/TD]
[TD]50
Breast[/TD]
[TD]50
Free[/TD]
[TD]50
Fly[/TD]
[TD]100
Back[/TD]
[TD]100
Breast[/TD]
[TD]100
Free[/TD]
[TD]100
Fly[/TD]
[TD]100
IM[/TD]
[TD]200
Back[/TD]
[TD]200
Breast[/TD]
[TD]200
Free[/TD]
[TD]200
Fly[/TD]
[TD]200
IM[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Charlie Smith[/TD]
[TD]M[/TD]
[TD]31/01/2013[/TD]
[TD]10[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]0:22.34[/TD]
[TD]0:24.45[/TD]
[TD]0:18.25[/TD]
[TD]0:25.34[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Bill Medley[/TD]
[TD]M[/TD]
[TD]31/08/2013[/TD]
[TD]10[/TD]
[TD]E[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]0:31.23[/TD]
[TD]0:28.93[/TD]
[TD]0:21.34[/TD]
[TD]0:28.45[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

IF a new swimmer entered the club this sheet would update when the new swimmers details are entered to show that they are able to enter this event. This will help the main coaches when trying to pick teams etc.

Also we have created a role for a new parent as meet organiser and they would access this sheet and be able to track if a parent has been informed of the gala and if the child will be swimming and has paid etc, I place to just put Tick boxes at the end of the fields above, but realise now this might be extremely complicated as the pulled through data will always change and I am unsure if it would be capable of keeping static data next to the right swimmer name. I have deleted some columns just to show the extra tick boxes that I am wanting to enter to track the extra information.

1. Boys 9/10yrs 2l Freestyle

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SwimmerName[/TD]
[TD]Sex[/TD]
[TD]DOB[/TD]
[TD]AOD[/TD]
[TD]Squad[/TD]
[TD]COBSC[/TD]
[TD]Active[/TD]
[TD]25
Back[/TD]
[TD]25
Breast[/TD]
[TD]25
Free[/TD]
[TD]25
Fly
[/TD]
[TD]50
Back[/TD]
[TD]50
Breast[/TD]
[TD]50
Free[/TD]
[TD]50
Fly[/TD]
[TD]100
Back[/TD]
[TD]100
Breast[/TD]
[TD]100
Free[/TD]
[TD]100
Fly[/TD]
[TD]100
IM[/TD]
[TD]200
Back[/TD]
[TD]Swimming
Y/N[/TD]
[TD]Paid
Y/N[/TD]
[/TR]
[TR]
[TD]Charlie Smith[/TD]
[TD]M[/TD]
[TD]31/01/2013[/TD]
[TD]10[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]0:22.34[/TD]
[TD]0:24.45[/TD]
[TD]0:18.25[/TD]
[TD]0:25.34[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]y[/TD]
[TD]n[/TD]
[/TR]
[TR]
[TD]Bill Medley[/TD]
[TD]M[/TD]
[TD]31/08/2013[/TD]
[TD]10[/TD]
[TD]E[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]0:31.23[/TD]
[TD]0:28.93[/TD]
[TD]0:21.34[/TD]
[TD]0:28.45[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 256"]
<tbody>[TR]
[TD="colspan: 3"]2. Girls 9/10yrs 2L Freestyle

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SwimmerName[/TD]
[TD]Sex[/TD]
[TD]DOB[/TD]
[TD]AOD[/TD]
[TD]Squad[/TD]
[TD]COBSC[/TD]
[TD]Active[/TD]
[TD]25
Back[/TD]
[TD]25
Breast[/TD]
[TD]25
Free[/TD]
[TD]25
Fly
[/TD]
[TD]50
Back[/TD]
[TD]50
Breast[/TD]
[TD]50
Free[/TD]
[TD]50
Fly[/TD]
[TD]100
Back[/TD]
[TD]100
Breast[/TD]
[TD]100
Free[/TD]
[TD]100
Fly[/TD]
[TD]100
IM[/TD]
[TD]200
Back[/TD]
[TD]Swimming
Y/N[/TD]
[TD]Paid
Y/N[/TD]
[/TR]
[TR]
[TD]Louise Tarka[/TD]
[TD]F[/TD]
[TD]31/01/2013[/TD]
[TD]10[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]0:22.34[/TD]
[TD]0:24.45[/TD]
[TD]0:18.25[/TD]
[TD]0:25.34[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]y[/TD]
[TD]n[/TD]
[/TR]
[TR]
[TD]Sarah Fish[/TD]
[TD]F[/TD]
[TD]31/08/2013[/TD]
[TD]10[/TD]
[TD]E[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]0:31.23[/TD]
[TD]0:28.93[/TD]
[TD]0:21.34[/TD]
[TD]0:28.45[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The key thing is when I bring the new information across I want it to always be upto date referring back to its original location to ensure fastest times are always shown. I have tried the formula process and realised what I am trying to do is far too complicated.

Any guidance or help would be greatly appreciated, thank you for taking the time to read this far.

Andy.