Refering to specific entry in a drop down list

[davidstrongarm]

I'm creating an excel document to record all the statistics from a driving championship but I've run into a problem, this is hard for me to explain but I'll do my best...

I have a worksheet called 'Names' which has a list of driver names on it.
I then have a worksheet for each race, with all the results.

On a worksheet called 'DriverStats', I have a drop down menu made up of all the driver names, and depending on the selected driver all of the stats on this worksheet change appropriately.

But, I now want another worksheet 'Tables', which displays stats for several drivers together. If I make a reference to the 'DriverStats' worksheet, then of course the value returned depends on whoever happens to be selected in the drop down menu.

Is there a way of referring to a cell on the 'DriverStats' worksheet, AND tell excel to return the value based on a specific selection from the drop down menu on that worksheet?

The problem is, most of the statistics on the 'DriverStats' sheet have very complex forumlas, and often one stat effects other stats, so I don't want to have re enter any complex formulas on the 'Tables' sheet, rather I would like to simple use a cell reference.

I hope that is clear heh

Any help would be much appreciated.

Cheers,
Dave.

[Richard Buttrey]

Hi Dave,

The answer is probably yes but we really need to see your workbook in order to advise further. Would you upload it?

Regards

[davidstrongarm]

Ok I've attached the document.

Here's the problem - on the 'Driver Statistics' worksheet, cell D2 is a drop down menu with a list of drivers. You can see, for example, that driver Ben Phillips has made 3 starts.

On the worksheet 'Tables', I would like to list how many starts each driver has made, so cell C8 should show 3 (as Ben Phillips has made 3 starts).

But obviously, I can't just put ='Driver Statistics'!D6 into C8, because as soon as I change my selection in the drop down menu the value changes.

So is there some way to constantly refer to the value of cell 'Driver Statistics'!D6 for Ben Phillips, regardless of who is selected in the drop down menu?

Thanks.

[Richard Buttrey]

Hi,

See the attached for one approach.

Regards

[davidstrongarm]

Ok I see what you've done. Your help is very much appreciated Richard!

But what I wanted to do was have the worksheet you edited, contain various stats, not just the starts. If you look at the new workbook I've uploaded you'll see what I mean.

I'm beginning to think that I will either have to create a worksheet for each driver, or completely re think the design of the workbook.

All this excel stuff gives me a headache

[Richard Buttrey]

Hi David,

OK understood.

I was going to suggest that you would simplify your task immensely if you redesigned your basic data so that it was in a single simple database table. This approach allows you to analyse / summarise data more easily and more importantly it facilitates the use of Pivot Tables.

Once you have the basic design working you can then add bells and whistles like a data entry form which you could use to add new data to the database.

I'm attaching a suggested approach.

Note that I've left out of the database any Team references since it seems to me that these can be implied by using a VLOOKUP where necessary, and it would be better to add an extra column to your drivers tables in the Codes sheet cols B:D for this.

Hope this gives you some ideas.

Rgds

[davidstrongarm]

Ok I'll have a deeper look into pivot tables and re think the design. This is the first workbook I've ever really worked on and I've still a lot to learn.

Thank you very much for all of your help!