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

 

[Andrew Poulsom]

Welcome to MrExcel.

It seems to me that you should be using lookup tables:

http://spreadsheetpage.com/index.php/tip/alternatives_to_nested_if_functions/

 

[adatom]

Thank you for the prompt reply but I need a little more direction, sorry!

 

[AlphaFrog]

First:

=IF(OR(ISERROR(((C8-C10)>(D7-1)) + ((C8-C10)>(D7+8))),((C8-C10)>(D7-1)) + ((C8-C10)>(D7+8))=0, D7=0, D7>9),"",
((C8-C10)>(D7-1)) + ((C8-C10)>(D7+8)))

Second:

=IF(ISERROR(VLOOKUP(B8,$R$2:$S$56,2,0)),"",VLOOKUP(B8,$R$2:$S$56,2,0))

 

[adatom]

Thank You! The first one works great but the second did not.

Heres the table that I have pulling in from other sheets:

Excel Workbook
	U	V	W
1	Team 1	Adam	5
2	Team 1	Bob	11
3	Team 2	Steve	7
4	Team 2	Larry	9
5	Team 3	Tom	2
6	Team 3	Chris	5
Front

Excel 2003

Cell Formulas
Range	Formula
V1	='Raw Player List'!A1
V2	='Raw Player List'!A2
V3	='Raw Player List'!A3
V4	='Raw Player List'!A4
V5	='Raw Player List'!A5
V6	='Raw Player List'!A6
W1	='Raw Player List'!B1
W2	='Raw Player List'!B2
W3	='Raw Player List'!B3
W4	='Raw Player List'!B4
W5	='Raw Player List'!B5
W6	='Raw Player List'!B6

Maybe that will help you, help me, with the vlookup function you tried to create. I would love it if it allowed me to select a team from a list in D2 and have the name pop up along with their average.

Thanks again!

 

[Marcol]

See if this works for the first, it's a tad shorter.

=IF(AND(D7>0,D7<10,ISNUMBER(C8-C10)),IF(C8-C10>D7-1,1,0)+IF(C8-C10>D7+8,1,0),"")

 

[adatom]

Thanks for the reply but I have already tweaked the first one and applied it to every cell I needed it in, any advice on the second?

 

[adatom]

Here's the kicker on when I look up a Team in Cell D2, underneath in D3 and D4 there are spots for "subs" and overrides the name that comes in with the Team picked in D2 and replaces it.

Make sense?

I wish I had the code but I am at work while my Mac sits at home.

 

[adatom]

Here's a fraction of the scorecard:

Excel Workbook
	A	B	C	D	E	F
2				Team 1
3			"A" Subs
4			"B" Subs
5	Front		Hole	1	2	3
6	WK 1	Black Tees	Yardage	0	0	0
7	Team	Players	Hdcp	8	4	3
8	Team 1		7		1	1
Front

Excel 2003

In D2 the Team is picked, it then pulls in the persons name on that team in B8 while it inserts in Average into C8 and then the formulas in each hole cell figures witch holes he gets strokes on against his competitor. Also, if I sub is needed, you pick them from a list and there name gets inserted over top of the original team member and pulls in their average.

 

[adatom]

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.