Need help with References for an Excel NASCAR Pool worksheet

**Pat Parker** · 02-22-2010, 12:13 PM

I usually find an answer online but this one has me stumped. Here is the sheet:

http://www.hvlistings.com/NASCAR2010.XLS

I plan to enter races on the Races sheet. Each race takes up 2 columns (Driver and Points for that race). I then want to use INDEX and MATCH to get the points onto the TEAMS page. If you look at Row 7 of the TEAMS page, it works OK until I get to Race 14. This is because the columns are now AA, AB, etc and the CHAR function does not work. Is there a better, cleaner way to do this? I put scores for Jimmy Johnson all the way through the 36 races (each score corresponds with the race #), so it is easy to test a fix.

Thanks in advance!

**DonkeyOte** · 02-22-2010, 12:28 PM

I'd suggest you avoid INDIRECT altogether given it's Volatile and the same can be achieved using non-volatile INDEX construct.

Given results are numeric it makes sense to use SUMIF - this will also account for drivers who may not be listed for whatever reason by returning 0 (and therefore negating need for error handler)

F7: =SUMIF(INDEX(Races!$A$1:$BU$55,0,1+(F$1-1)*2),$D7,INDEX(Races!$A$1:$BU$55,0,F$1*2))
copied across F7:AO15
(F7:AO15 copied to other sub tables as necessary)

on an aside people generally prefer you upload your attachments direct

**Pat Parker** · 02-22-2010, 12:52 PM

It worked perfectly - thank you for the excellent advice. I also appreciate you answering so quickly. Thanks!

**DonkeyOte** · 02-22-2010, 12:59 PM

You're welcome but per my note at MrExcel - if you opt to x-post on multiple forums we would ask you disclose all relevant links so as to reduce possibility of people wasting their time - ie answering something that might already have been resolved elsewhere.

**royUK** · 02-22-2010, 02:18 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

X-posted here

**Pat Parker** · 02-25-2010, 02:04 PM

Got it. Thanks.

**racnfan** · 02-29-2012, 06:25 PM

Most NASCAR race pools allow for a spare driver.

How would you change the formula to use a spare driver for a not raced driver?

Attachment shows Ron Fellows did not race in recent Daytona 500, want to use McMurray's finish instead.