Can't think of a title for this one :D

I have an Excel spreadsheet of fastest lap times for different drivers on different tracks in different cars.

One Worksheet per driver with the Track down the side, the car type across the top and the lap times in the relevant row/column. In all there are 10 (I think) worksheets.

I would like to use a blank sheet with just the car types and tracks, and to use a function to find the fastest lap out of all the drivers using a particluar car/track combination and to report that drivers name.

Clear as mud I know, but is this possible?

Thanks in advance

Assuming that the worksheets for each driver is set-up like the first attachment (Driver.jpg), and that each sheet is named after the driver, a summary sheet can be set up like the second attachment (Summary.jpg) using the following formulas...

B2, copied down and across:

=MAX('Driver 1:Driver 10'!B2)

B15, copied down and across:

=INDEX($H$2:$H$4,MATCH(B2,N(INDIRECT("'"&$H$2:$H$11&"'!"&CELL("address",B2))),0))

...confirmed with CONTROL+SHIFT+ENTER, and where H2:H11 contains your sheet/driver names.

Note that if there are two or more drivers with the fastest time for any track and car type combination, only the first occurrence will be returned.

Hope this helps!

Originally Posted by EARTHWALKER

I have an Excel spreadsheet of fastest lap times for different drivers on different tracks in different cars.

One Worksheet per driver with the Track down the side, the car type across the top and the lap times in the relevant row/column. In all there are 10 (I think) worksheets.

I would like to use a blank sheet with just the car types and tracks, and to use a function to find the fastest lap out of all the drivers using a particluar car/track combination and to report that drivers name.

Clear as mud I know, but is this possible?

Thanks in advance

I'll give that a try. Many thanks Dom.

Ok, firstly the sheets are made out the way you have them :D

The tracks go down, A2 to A27
The cars go from B1 to I1

There are 9 drivers and the sheets are their names, so sheet 1 = Bean0 2= Elkdanger and so on.

I tried exactly what you showed and all I get is a #ref
I also tried changine Driver1: Driver2 in your formula to Bean0 and Elkdanger and still I get a #ref.

I can't work it out.

Just had a bit of a play on the end sheet.
What I've got now is this formula: MIN(Bean0!B2:I2), it has returned a fastest time for that driver on that track. What I want that to do now is in another cell do the same, but it then look at the fastest time and go up one and return the car used.

Once I can get that, it will be easy to do it for all of them and then do a comparisom of the guy with the fastest time on a particular track, what car he used and what the actual time was.

Any help would be greatly appreciated. I can send the file to someone if they like...it's not top secret or anything :D

If you'd like you can send me your file and I'll see if I can help. You can email me at [email protected].

Cheers!

Originally Posted by EARTHWALKER

Just had a bit of a play on the end sheet.
What I've got now is this formula: MIN(Bean0!B2:I2), it has returned a fastest time for that driver on that track. What I want that to do now is in another cell do the same, but it then look at the fastest time and go up one and return the car used.

Once I can get that, it will be easy to do it for all of them and then do a comparisom of the guy with the fastest time on a particular track, what car he used and what the actual time was.

Any help would be greatly appreciated. I can send the file to someone if they like...it's not top secret or anything :D

Thnak you m8. It's on the way to you now.

Originally Posted by EARTHWALKER

Thnak you m8. It's on the way to you now.

Just wanted to confirm that I emailed you earlier.

Cheers!