Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

Hi,

Why not extract the top 10 names as described yesterday and then query those
names to get the date.

For example my top ten names are extracted into another sheet from Sick
History into column F so next to the top name type the formula:-

=VLOOKUP(F1,'Sick History'!A$3:B$220,2,FALSE)

This looks in column A for the name and returns the date next to it in
column B. Drag it down to get the date for all names.

Mike

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

Hi,

Follow procedure 1 and procedure 2 below.

Procedure 1:

Filter by using advanced criteria

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. On the Data menu, point to Filter, and then click Advanced Filter.

4. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

5. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog
6. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Procedure 2:

Sort the extracted data, hide the relevant columns.

For more information about Advanced Filter, refer to the online help.

Challa Prabhu

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

Mike
Sorry to be a pain and thank you for yesterday
This what i have so far

All data held in sheet "Sickness" as previously explained
So far i have manged to get the top ten Names and scores to appear in the
sheet Top Ten as follows

Column B4 to B13 lists the names
Column D4 to D13 lists the scores

I now would like the corresponding Year data to appear in C4 to C13
At the moment when i use the calualation you gave it just repeats the same
year over and over



"Mike H" wrote:

Hi,

Why not extract the top 10 names as described yesterday and then query those
names to get the date.

For example my top ten names are extracted into another sheet from Sick
History into column F so next to the top name type the formula:-

=VLOOKUP(F1,'Sick History'!A$3:B$220,2,FALSE)

This looks in column A for the name and returns the date next to it in
column B. Drag it down to get the date for all names.

Mike

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

That looked like it was a good answer thank you for replying
The problem i have it that i let quite a few people have read only access
and would prefer that there is something that just automatically up dates the
sheet
I'm two thirds there with the information Mike has given i just need the
last bit answering about the displaying the correct year as well.

If you could answer that i would appreciate it

"challa prabhu" wrote:

Hi,

Follow procedure 1 and procedure 2 below.

Procedure 1:

Filter by using advanced criteria

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. On the Data menu, point to Filter, and then click Advanced Filter.

4. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

5. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog
6. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Procedure 2:

Sort the extracted data, hide the relevant columns.

For more information about Advanced Filter, refer to the online help.

Challa Prabhu

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

Hi,

Clearly I haven't understood your data layout. Would you like to post the
file (or an extract from it) showing your data layout.

http://savefile.com/

Mike



"louiscourtney" wrote:

That looked like it was a good answer thank you for replying
The problem i have it that i let quite a few people have read only access
and would prefer that there is something that just automatically up dates the
sheet
I'm two thirds there with the information Mike has given i just need the
last bit answering about the displaying the correct year as well.

If you could answer that i would appreciate it

"challa prabhu" wrote:

Hi,

Follow procedure 1 and procedure 2 below.

Procedure 1:

Filter by using advanced criteria

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. On the Data menu, point to Filter, and then click Advanced Filter.

4. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

5. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog
6. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Procedure 2:

Sort the extracted data, hide the relevant columns.

For more information about Advanced Filter, refer to the online help.

Challa Prabhu

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

Mike
The example file is held here
http://www.savefile.com/files/903202

"Mike H" wrote:

Hi,

Clearly I haven't understood your data layout. Would you like to post the
file (or an extract from it) showing your data layout.

http://savefile.com/

Mike



"louiscourtney" wrote:

That looked like it was a good answer thank you for replying
The problem i have it that i let quite a few people have read only access
and would prefer that there is something that just automatically up dates the
sheet
I'm two thirds there with the information Mike has given i just need the
last bit answering about the displaying the correct year as well.

If you could answer that i would appreciate it

"challa prabhu" wrote:

Hi,

Follow procedure 1 and procedure 2 below.

Procedure 1:

Filter by using advanced criteria

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. On the Data menu, point to Filter, and then click Advanced Filter.

4. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

5. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog
6. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Procedure 2:

Sort the extracted data, hide the relevant columns.

For more information about Advanced Filter, refer to the online help.

Challa Prabhu

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

Hi,

Fixed and posted back. I've also corrected some of the formula on the top 10
sheet that were giving spurious answers so check them out.


http://www.savefile.com/files/903224

Mike

"louiscourtney" wrote:

Mike
The example file is held here
http://www.savefile.com/files/903202

"Mike H" wrote:

Hi,

Clearly I haven't understood your data layout. Would you like to post the
file (or an extract from it) showing your data layout.

http://savefile.com/

Mike



"louiscourtney" wrote:

That looked like it was a good answer thank you for replying
The problem i have it that i let quite a few people have read only access
and would prefer that there is something that just automatically up dates the
sheet
I'm two thirds there with the information Mike has given i just need the
last bit answering about the displaying the correct year as well.

If you could answer that i would appreciate it

"challa prabhu" wrote:

Hi,

Follow procedure 1 and procedure 2 below.

Procedure 1:

Filter by using advanced criteria

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. On the Data menu, point to Filter, and then click Advanced Filter.

4. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

5. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog
6. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Procedure 2:

Sort the extracted data, hide the relevant columns.

For more information about Advanced Filter, refer to the online help.

Challa Prabhu

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

Mike absolutley perfect result well done and thank you

Shane

"Mike H" wrote:

Hi,

Fixed and posted back. I've also corrected some of the formula on the top 10
sheet that were giving spurious answers so check them out.


http://www.savefile.com/files/903224

Mike

"louiscourtney" wrote:

Mike
The example file is held here
http://www.savefile.com/files/903202

"Mike H" wrote:

Hi,

Clearly I haven't understood your data layout. Would you like to post the
file (or an extract from it) showing your data layout.

http://savefile.com/

Mike



"louiscourtney" wrote:

That looked like it was a good answer thank you for replying
The problem i have it that i let quite a few people have read only access
and would prefer that there is something that just automatically up dates the
sheet
I'm two thirds there with the information Mike has given i just need the
last bit answering about the displaying the correct year as well.

If you could answer that i would appreciate it

"challa prabhu" wrote:

Hi,

Follow procedure 1 and procedure 2 below.

Procedure 1:

Filter by using advanced criteria

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. On the Data menu, point to Filter, and then click Advanced Filter.

4. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

5. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog
6. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Procedure 2:

Sort the extracted data, hide the relevant columns.

For more information about Advanced Filter, refer to the online help.

Challa Prabhu

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated

You welcome, sack Shane and long live the Bradford Factor.

Mike

"louiscourtney" wrote:

Mike absolutley perfect result well done and thank you

Shane

"Mike H" wrote:

Hi,

Fixed and posted back. I've also corrected some of the formula on the top 10
sheet that were giving spurious answers so check them out.


http://www.savefile.com/files/903224

Mike

"louiscourtney" wrote:

Mike
The example file is held here
http://www.savefile.com/files/903202

"Mike H" wrote:

Hi,

Clearly I haven't understood your data layout. Would you like to post the
file (or an extract from it) showing your data layout.

http://savefile.com/

Mike



"louiscourtney" wrote:

That looked like it was a good answer thank you for replying
The problem i have it that i let quite a few people have read only access
and would prefer that there is something that just automatically up dates the
sheet
I'm two thirds there with the information Mike has given i just need the
last bit answering about the displaying the correct year as well.

If you could answer that i would appreciate it

"challa prabhu" wrote:

Hi,

Follow procedure 1 and procedure 2 below.

Procedure 1:

Filter by using advanced criteria

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. On the Data menu, point to Filter, and then click Advanced Filter.

4. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

5. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog
6. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Procedure 2:

Sort the extracted data, hide the relevant columns.

For more information about Advanced Filter, refer to the online help.

Challa Prabhu

"louiscourtney" wrote:

Let me try to explain
In work sheet called Sickhistory
I have columns A to P
In A3 down to A502 i have names
In B3 down to B502 i have Years
Then in columns C to O are columns that are recording data linked from other
sheets
In P3 down to P502 there are various different scores

What i want to be able to do is extract the top 10 scores data from Column P
including the name and years from columns A & B and then add them to a sheet
called Top 10 so that it is displayed something like this

Name Year score

I cannot use the filter button on worksheet Sickhistory as there are cells
of various sizes so it won't filter all the correct information

Any help with this would be extremely appreciated