OK, here is what I want to do. I play an online golf game where they run lots of different events (laugh it up). Anyway, I am trying to automate the scoring system we have in place as much as possible. I am running into the following issue:



I want to be able to look at the team that is playing (located in Cell D6 and D7) and reference the list of teams to the right, and then automatically take "Golfer 1" and place him and his handicap in the appropriate boxes (in my illustration it would be putting Jane in Cell E11 and her handicap in Cell D11).

I have tried using a series of nested IF statements, which works GREAT except it only allows me to nest 7 IF's, and I need to be able to nest 15 IF's.

I also tried the following formula: =IF(D6=X9:X24,Y9:Y24,"") Basically my intent is to say "Hey, if D6 matches any of those team numbers, take the name adjacent to it and fill in the box." But this doesn't work for some reason.

Can someone help please?

[Edited on March 9, 2009 at 4:35 AM. Reason : ssssss]

3/9/2009 4:34:15 AM

1. Techtalk
2. wouldn't vlookups work?

golfer name = vlookup(team no#, team column, golfer column)
HCP = vlookup(golfer name, golfer column, HCP column)

and you get the idea

[Edited on March 9, 2009 at 8:23 AM. Reason : .]

3/9/2009 8:23:13 AM

Quote :

"golfer name = vlookup(team no#, team column, golfer column) HCP = vlookup(golfer name, golfer column, HCP column)"

forgot to mention - the last parameters in each function would be the column that you want to return data from (numbered starting at the reference coumn, i.e. team column)- i.e., to return golfer name, input 2, to return HCP, input 3

3/9/2009 9:05:22 AM

i think you have a heating problem

3/9/2009 11:27:57 AM

Thanks wdprice3! I had no idea that function existed.

3/9/2009 12:38:19 PM

I'm guessing that worked for you? The explanation I gave wasn't great - I was still trying to wake up

To make it fully automated, did you set all the parameters as cell references - that would be the best case, in case you ever add/remove/change golfers/names/HCPs/etc. (the only thing that isn't a cell reference should be the the column index (1,2, etc)

For example the first golfer would be:

C11 = VLOOKUP(D6,X6:Y24,2,FALSE)
D11 = VLOOKUP(D11,Y6:Z24,FALSE)

and you can play around with the $'s to be able to drag formulas, if you want.

[Edited on March 10, 2009 at 12:21 AM. Reason : .]

3/10/2009 12:14:57 AM

Yes I did all that. I am fairly good with Excel... I assumed there had to be some kind of function that would do what I wanted I just didn't know what it was and Vista does not support Help.

3/10/2009 4:48:28 AM

awesome, glad to hear.

and vista doesn't support help? I'm guessing you mean excel's help doesn't really help you and not 'excel help doesn't work on vista'? because office help does run on vista...

[Edited on March 10, 2009 at 10:26 AM. Reason : .]

3/10/2009 10:25:24 AM

I have a copy of Office 97 I installed (I have used it on other computers in the past) and for some reason the help feature does not work. The little paperclip dude pops up but then when I click on any links he provides it says Vista does not support help.... I am thinking it may be an Office 97 thing.

3/12/2009 2:04:40 PM