#N/A

[caldog]

I am pulling some stat information off the web, into Excel spreadsheet. My
problem is there will be at times that a player will not play, and when this
happens my formula keeps coming up with a #N/A error.

My code is as follows:

=IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM
ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game
Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game
Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))


The {[Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER"} is for me to look for
the team name that I’m tracking.

The {ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))}
is for me to see if a particular player has played in this game. I’m looking
for a player number in roster column for that game, if player did not play,
then put down a zero under that game, then see next line.

The {(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))}
this part of the function will fill in the number for that particular
category if player has played this game.

$A$2 is the player number location on my spreadsheet. $J$8:$J$25 is the
range of player numbers that have played in the game.

Example: Say player number 23 played in the game, what I want my formula to
do is take player number (from my spreadsheet) and divide by player number
(from web information) to come up with one. Likewise if player number 23 did
not play then a zero would be inputted.

Any help in figuring out why I am getting a #N/A formula error every time a
player HAS NOT played would be greatly appreciated.

Steve

 

[Sasa Stankovic]

hy,
maybe you should try with "tolls" - "formula auditing" - "evaluate formula"
this way excel will guide you untill process of calculating your result is
done.
For VLOOKUP error handling you can use ISNA function.

 

[caldog]

Sasa before posting to this fourm I tried that option. but it was no help at
all.

I know what the problem is, Vlookup in not able to locate the number, but
what I don't know is how to is create a formula to overcome this problem.

Steve

hy,
maybe you should try with "tolls" - "formula auditing" - "evaluate formula"
this way excel will guide you untill process of calculating your result is
done.
For VLOOKUP error handling you can use ISNA function.

I am pulling some stat information off the web, into Excel spreadsheet. My
problem is there will be at times that a player will not play, and when
this
happens my formula keeps coming up with a #N/A error.

My code is as follows:

=IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM
ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game
Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game
Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))


The {[Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER"} is for me to look
for
the team name that I'm tracking.

The {ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))}
is for me to see if a particular player has played in this game. I'm
looking
for a player number in roster column for that game, if player did not
play,
then put down a zero under that game, then see next line.

The {(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))}
this part of the function will fill in the number for that particular
category if player has played this game.

$A$2 is the player number location on my spreadsheet. $J$8:$J$25 is the
range of player numbers that have played in the game.

Example: Say player number 23 played in the game, what I want my formula
to
do is take player number (from my spreadsheet) and divide by player number
(from web information) to come up with one. Likewise if player number 23
did
not play then a zero would be inputted.

Any help in figuring out why I am getting a #N/A formula error every time
a
player HAS NOT played would be greatly appreciated.

Steve

 

[Max]

Instead of:

=IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM

ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game
Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game
Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))

With Game Stats-1.xls simultaneously open,

Try this simpler alternative in the destination sheet:
=IF('[Game Stats-1.xls]Game-1'!$I$6="MY TEAM
ROSTER",IF(ISNA(MATCH($A$2,'[Game Stats-1.xls]Game-1'!$J$8:$J$25,0)),"",1),"")

Believe it achieves your underlying intents

 

[caldog]

Max

Thanks so much that works just perfectly.

Steve

Instead of:

=IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM

ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game
Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game
Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))

With Game Stats-1.xls simultaneously open,

Try this simpler alternative in the destination sheet:
=IF('[Game Stats-1.xls]Game-1'!$I$6="MY TEAM
ROSTER",IF(ISNA(MATCH($A$2,'[Game Stats-1.xls]Game-1'!$J$8:$J$25,0)),"",1),"")

Believe it achieves your underlying intents

 

[Max]

Steve, you're welcome !