Excel lookup - Index/Match/Indirect help needed

[rocket_rob_71]

I am attempting to assemble data for my online Nascar racing league. W
want to show a drivers progress throught the season from race to rac
(Title position and race finish) in an easy to read chart. Here is a
example of what we are trying for:
http://i.cnn.net/nascar/drivers/dps/mkenseth00/cup/images/mkenseth00_2003_statsChart.gif

We automatically generate the race results for each race and publis
them on our web site. My excel workbook has opened each web page tha
I want to compile the data from. Example: We have raced at three race
(Daytona, Bristol, and Richmond). I have a workbook with four sheet
named "Summary", "Daytona", "Bristol" and "Richmond". I am using th
following formula to grab data for a given car number and copy it ove
to the summary sheet. (The summary sheet is used for the data neede
for the chart.)

=INDEX(Bristol!A2:O14,MATCH($A$3,Bristol!D214,0),1)

$A$3 is the car # it matches info on the Bristol sheet. Once matched
it copies over the data for the specified car number in colum A fro
the data in colum D. I want to replace the hardcoded sheet nam
"Bristol" with a string so I can match data from what ever shee
matches cell B1 on the summary sheet. Example if we race a
"California", I want to type "California" in cell B1 and i
lookup/match the data for car ($A$3) from the "California" sheet .
tried using Indirect, but my results were "#N/A".

Also, if a driver hasn't competed in a race, I need a way to show zero
instead of "#N/A".

Any help would be appreciated

 

[Frank Kabel]

Hi
if the sheet name is entered in B1 try the following formula for your
INDEX function:
=INDEX(INDIRECT("'" & B1 & "'!A2:O14"),MATCH($A$3,INDIRECT("'" & B1 &
"'!D214"),0),1)

Note the multiple apostrophes within the INDIRECT functions:
"'" is with spaces " ' "
"' is with spaces " '

For the second problem (#NA error) try the following
IF(ISNA(INDEX(....),0,INDEX(....))

HTH
Frank