Formatting and moving data to another sheet via Vlookup? or VBA ? or Something Else

A

Andy Donegan

New Member
Joined
Sep 2, 2013
Messages
13
Hi I am currently trying to produce a spreadsheet for an entry system to sort out swimmers in a gala by events they have entered. Currently I have the main entry sheet which I receive back from each swimming club, with the details entered, Columns F to J are y / n answer if Y is selected the relevant matching box in K to O highlights from solid black to green for a time to be entered.
What I a hoping to achieve is once I have received all entrys from every club, to copy the info from the main entrys page below on to each specific event TAB, I will create 25 TABS for all the events.
I have created the output that I am hoping to get and pasted that in below as Event1.

Event 1 Tab Below shows the data I would like to pull through from the main sheet above. The top box is my criteria, so I want all males aged 9 entered for the backstroke. It does not need to filter on 50m, that is just there for the event description.
The box below is still event 1 but for a different age group, males, aged 10 backstroke.
I have searched and read a lot but found many different ways, but I think that with my limited knowledge I am struggling. I was hoping to produce this with a vlookup and if statement, but then I receive lots of blanks.
My main hope is if each event page has the top box with Sex, Age and stroke filter, I can run an if statement or the like to go to Entry Form and list all swimmers who picked yes for that event, and pull through the details.

I have played all last night and this morning and can not get anything near to what I am looking for, if somebody has the patience or time to read my request and point me in the correct direction it would be greatly appreciated.
Please note I have used VBA once now thanks to this forum when a problem was solved by somebody, but I am a novice to the technical stuff, I learn quick and will try solutions.
I also tried advanced filter, but it became messy, I just could not achieve the output I require to help us make the gala planning easier.

Also we do have gala planning software, our problem is that it is so complicated nobody can use it with out the aid of one person, a spreadsheet will make so much easier.

Thank you very much in advance.


Excel 2010
ABCDEFGHIJKLMNO
Swimmer Name
name 1
name 2
name 3
name 4
name 5
name 6
name 7
name 8

<tbody>
[TD="align: center"]4[/TD]

[TD="align: center"]Date of Birth[/TD]
[TD="align: center"]Male or Female[/TD]
[TD="align: center"]Name of Club[/TD]
[TD="align: center"]Age On Day[/TD]
[TD="align: center"]Backstroke[/TD]
[TD="align: center"]Breaststroke[/TD]
[TD="align: center"]Freestyle[/TD]
[TD="align: center"]Butterfly[/TD]
[TD="align: center"]IM[/TD]
[TD="align: center"]Backstroke[/TD]
[TD="align: center"]Breaststroke[/TD]
[TD="align: center"]Freestyle[/TD]
[TD="align: center"]Butterfly[/TD]
[TD="align: center"]IM[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]25/05/2002[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]13/03/1999[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]21/01/2005[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]18/02/2004[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]Ilkley SC[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]11/03/2005[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]Ilkley SC[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]16/05/1999[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]Bingley SC[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]26/11/1999[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]Bingley SC[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]11/06/2001[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]Bradford Dolphins SC[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]
[TD="bgcolor: #C4D79B, align: right"]00:00.0[/TD]

</tbody>
Entry Form​


Excel 2010
ABCDEF
name 4
name 24
name 25
name 26
name 27
name 13
name 29
name 40
name 48

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sex[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]Distance[/TD]
[TD="align: center"]Stroke[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]EVENT 1[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]50m[/TD]
[TD="align: center"]Backstroke[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Swimmer Name[/TD]
[TD="align: center"]DOB[/TD]
[TD="align: center"]Club[/TD]
[TD="align: center"]Entry Time[/TD]
[TD="align: center"]Time Swam[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18/02/2004[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]05/03/2004[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10/05/2004[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]04/02/2004[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]05/03/2004[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sex[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]Distance[/TD]
[TD="align: center"]Stroke[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]EVENT 2[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50m[/TD]
[TD="align: center"]Backstroke[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Swimmer Name[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]Club[/TD]
[TD="align: center"]Entry Time[/TD]
[TD="align: center"]Time Swam[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23/03/2003[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]04/05/2003[/TD]
[TD="align: center"]Bradford SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]05/12/2003[/TD]
[TD="align: center"]Ilkley SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]07/08/2003[/TD]
[TD="align: center"]Keighley SC[/TD]
[TD="align: center"]00:00.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>
Event1​
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA: Extracting raw data and move into a new Sheet (tidy format)
Replies
9
Views
746
Chanzuihou
C
A
  • Question Question
Copying and Filtering Data and keeping new sheet updated.
Replies
0
Views
614
Andy Donegan
A
M
  • Question Question
VBA Code Populate Certain Column Data into Rows in another sheet
2
Replies
12
Views
3K
Zack Barresse
Zack Barresse

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top