Making Golf program wrote in 2011 work in 2003

[adatom]

Hello,

New poster here and I apologize if I do it wrong but I could really use some help.

I made an Excel spreadsheet on my Mac with 2011, which has the ability to nest as many IF functions you want, and I need it to work on my office computer which has Office 2003.

The formulas are simple, repetitive IF functions, with greater and less than and also the IFERROR.

Not sure how to post this so the three functions are below, the first is in each of the scoring spots of the scorecard and takes into account the hold handicap and the person handicap to arrive at a number of strokes each competitor receives.

The second, goes into the handicap cell thats linked to the third function, to pull in the correct handicap depending on which team is selected.

The third is a simple =if(d2='Team 1',R1,if(d2='team 2',r2,if(d2='team 3',r3....... up to Team 18

the first:

'=IF(ISERROR(IF(D7=1,IF(C8-C10>0,1,0)+IF(C8-C10>9,1,0),IF(D7=2,IF(C8-C10>1,1,0)+IF(C8-C10>10,1,0),IF(D7=3,IF(C8-C10>2,1,0)+IF(C8-C10>11,1,0),IF(D7=4,IF(C8-C10>3,1,0)+IF(C8-C10>12,1,0),IF(D7=5,IF(C8-C10>4,1,0)+IF(C8-C10>13,1,0),IF(D7=6,IF(C8-C10>5,1,0)+IF(C8-C10>14,1,0),IF(D7=7,IF(C8-C10>6,1,0)+IF(C8-C10>15,1,0),IF(D7=8,IF(C8-C10>7,1,0)+IF(C8-C10>16,1,0),IF(D7=9,IF(C8-C10>8,1,0)+IF(C8-C10>17,1,0))))))))))),"",IF(IF(D7=1,IF(C8-C10>0,1,0)+IF(C8-C10>9,1,0),IF(D7=2,IF(C8-C10>1,1,0)+IF(C8-C10>10,1,0),IF(D7=3,IF(C8-C10>2,1,0)+IF(C8-C10>11,1,0),IF(D7=4,IF(C8-C10>3,1,0)+IF(C8-C10>12,1,0),IF(D7=5,IF(C8-C10>4,1,0)+IF(C8-C10>13,1,0),IF(D7=6,IF(C8-C10>5,1,0)+IF(C8-C10>14,1,0),IF(D7=7,IF(C8-C10>6,1,0)+IF(C8-C10>15,1,0),IF(D7=8,IF(C8-C10>7,1,0)+IF(C8-C10>16,1,0),IF(D7=9,IF(C8-C10>8,1,0)+IF(C8-C10>17,1,0))))))))))=0,"",IF(D7=1,IF(C8-C10>0,1,0)+IF(C8-C10>9,1,0),IF(D7=2,IF(C8-C10>1,1,0)+IF(C8-C10>10,1,0),IF(D7=3,IF(C8-C10>2,1,0)+IF(C8-C10>11,1,0),IF(D7=4,IF(C8-C10>3,1,0)+IF(C8-C10>12,1,0),IF(D7=5,IF(C8-C10>4,1,0)+IF(C8-C10>13,1,0),IF(D7=6,IF(C8-C10>5,1,0)+IF(C8-C10>14,1,0),IF(D7=7,IF(C8-C10>6,1,0)+IF(C8-C10>15,1,0),IF(D7=8,IF(C8-C10>7,1,0)+IF(C8-C10>16,1,0),IF(D7=9,IF(C8-C10>8,1,0)+IF(C8-C10>17,1,0))))))))))))'

and the second

=IF(ISERROR(IF(B8=$R$2,$S$2,IF(B8=$R$3,$S$3,IF(B8=$R$4,$S$4,IF(B8=$R$5,$S$5,IF(B8=$R$6,$S$6,IF(B8=$R$7,$S$7,IF(B8=$R$8,$S$8,IF(B8=$R$9,$S$9,IF(B8=$R$10,$S$10,IF(B8=$R$11,$S$11,IF(B8=$R$12,$S$12,IF(B8=$R$13,$S$13,IF(B8=$R$14,$S$14,IF(B8=$R$15,$S$15,IF(B8=$R$16,$S$16,IF(B8=$R$17,$S$17,IF(B8=$R$18,$S$18,IF(B8=$R$19,$S$19,IF(B8=$R$20,$S$20,IF(B8=$R$21,$S$21,IF(B8=$R$22,$S$22,IF(B8=$R$23,$S$23,IF(B8=$R$24,$S$24,IF(B8=$R$25,$S$25,IF(B8=$R$26,$S$26,IF(B8=$R$27,$S$27,IF(B8=$R$28,$S$28,IF(B8=$R$29,$S$29,IF(B8=$R$30,$S$30,IF(B8=$R$31,$S$31,IF(B8=$R$32,$S$32,IF(B8=$R$33,$S$33,IF(B8=$R$34,$S$34,IF(B8=$R$35,$S$35,IF(B8=$R$36,$S$36,IF(B8=$R$37,$S$37,IF(B8=$R$38,$S$38,IF(B8=$R$39,$S$39,IF(B8=$R$40,$S$40,IF(B8=$R$41,$S$41,IF(B8=$R$42,$S$42,IF(B8=$R$43,$S$43,IF(B8=$R$44,$S$44,IF(B8=$R$45,$S$45,IF(B8=$R$46,$S$46,IF(B8=$R$47,$S$47,IF(B8=$R$48,$S$48,IF(B8=$R$49,$S$49,IF(B8=$R$50,$S$50,IF(B8=$R$51,$S$51,IF(B8=$R$52,$S$52,IF(B8=$R$53,$S$53,IF(B8=$R$54,$S$54,IF(B8=$R$55,$S$55,IF(B8=$R$56,$S$56,0)))))))))))))))))))))))))))))))))))))))))))))))))))))))),"",IF(B8=$R$2,$S$2,IF(B8=$R$3,$S$3,IF(B8=$R$4,$S$4,IF(B8=$R$5,$S$5,IF(B8=$R$6,$S$6,IF(B8=$R$7,$S$7,IF(B8=$R$8,$S$8,IF(B8=$R$9,$S$9,IF(B8=$R$10,$S$10,IF(B8=$R$11,$S$11,IF(B8=$R$12,$S$12,IF(B8=$R$13,$S$13,IF(B8=$R$14,$S$14,IF(B8=$R$15,$S$15,IF(B8=$R$16,$S$16,IF(B8=$R$17,$S$17,IF(B8=$R$18,$S$18,IF(B8=$R$19,$S$19,IF(B8=$R$20,$S$20,IF(B8=$R$21,$S$21,IF(B8=$R$22,$S$22,IF(B8=$R$23,$S$23,IF(B8=$R$24,$S$24,IF(B8=$R$25,$S$25,IF(B8=$R$26,$S$26,IF(B8=$R$27,$S$27,IF(B8=$R$28,$S$28,IF(B8=$R$29,$S$29,IF(B8=$R$30,$S$30,IF(B8=$R$31,$S$31,IF(B8=$R$32,$S$32,IF(B8=$R$33,$S$33,IF(B8=$R$34,$S$34,IF(B8=$R$35,$S$35,IF(B8=$R$36,$S$36,IF(B8=$R$37,$S$37,IF(B8=$R$38,$S$38,IF(B8=$R$39,$S$39,IF(B8=$R$40,$S$40,IF(B8=$R$41,$S$41,IF(B8=$R$42,$S$42,IF(B8=$R$43,$S$43,IF(B8=$R$44,$S$44,IF(B8=$R$45,$S$45,IF(B8=$R$46,$S$46,IF(B8=$R$47,$S$47,IF(B8=$R$48,$S$48,IF(B8=$R$49,$S$49,IF(B8=$R$50,$S$50,IF(B8=$R$51,$S$51,IF(B8=$R$52,$S$52,IF(B8=$R$53,$S$53,IF(B8=$R$54,$S$54,IF(B8=$R$55,$S$55,IF(B8=$R$56,$S$56,0))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Thank you for any and all help that is offered and again I apologize if this isnt the correct way to post

 

[AlphaFrog]

I got this to work:

Excel Workbook
	B
8	Adam
Front

Excel 2003

Cell Formulas
Range	Formula
B8	=IF(ISERROR(VLOOKUP(D2,$Q$1:$R$56,2,0)),"",VLOOKUP(D2,$Q$1:$R$56,2,0))

Problem, we run a two man team league and it always pulls in the first name while not recognizing the second "team 1" player.

Suggestions?

Also need help tweaking the above to pull in the subs when necessary, as described above.

First Team Player:
=IF(ISERROR(MATCH(D2,$Q$1:$Q$56,0)),"",INDEX($R$1:$R$56,MATCH(D2,$Q$1:$Q$56,0)))

Second Team Player: (assuming the two players are adjacent in the lookup table)
=IF(ISERROR(MATCH(D2,$Q$1:$Q$56,0)),"",INDEX($R$1:$R$56,MATCH(D2,$Q$1:$Q$56,0)+1))

INDEX MATCH - Excel Index Function and Excel Match Function

 

[Marcol]

From your original second formula, the only reason I can think off for AlphaFrogs' VLOOKUP() not working, is that your data is not sorted ascending on Column R.

Try this

=IF(ISNUMBER(MATCH($B8,$R$2:$R$56,0)),INDEX($R$2:$S$56,MATCH($B8,$R$2:$R$56,0),2),"")

[EDIT]
I'm loosing the place here, replies are to fast for me!

 

[adatom]

First Team Player:
=IF(ISERROR(MATCH(D2,$Q$1:$Q$56,0)),"",INDEX($R$1:$R$56,MATCH(D2,$Q$1:$Q$56,0)))

Second Team Player: (assuming the two players are adjacent in the lookup table)
=IF(ISERROR(MATCH(D2,$Q$1:$Q$56,0)),"",INDEX($R$1:$R$56,MATCH(D2,$Q$1:$Q$56,0)+1))

INDEX MATCH - Excel Index Function and Excel Match Function

Works great! better than breaking the two tables apart and doing to separate vlookups! Any idea on my final question (I hope) about the subs being pulled in?

 

[adatom]

OK, THIS is my last question. I have a weekly score sheet where I input points won and scores to figure placement and averages. I want the top three teams leading the league to get pulled to a 'dashboard' that I print out every week for people to see who won what and current weeks schedule.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Names	2011	1	2	3	4	5	6	7	Pos		First Half
4	Team 1	Pts>	24.0								###	1	Ave
5	Adam	5	38	42	48						Pts		43
6	Bob	11	52	51	48								50
Point Score Input

Excel 2003

Thats part of the score sheet while the dashboard isnt anything special. I've gotten close but y'all are so good I might as well learn from you! Thanks again!!!

 

[adatom]

Never mind the sub question, I was able to fix that by adding a simple IF function in front of the formula you gave me.

All that's left is the Dashboard! If any of you would like to see/use it, feel free to email me!