Excel VLOOKUP copy and paste error

[SantasLittleHelper]

I am trying to copy the following formula into multiple rows within the same column:
[FONT=&quot]=VLOOKUP(A2,'[Sheet1.xlsx]sheet1'!$A:$2:$AP$220,3,FALSE)

It seems to work, adding appropriate errors when no value can be returned but when I copy the formula down the column into different rows, it doesn't copy properly. It seems to appear as an error (Which is correct) until the first value can be returned. However the rows below this become the same value until a new value can be returned.

e.g.
[/FONT]

Returned Value	What should be returned
N/A	N/A
Yes	Yes
Yes	N/A
No	No
No	N/A

<tbody>
</tbody>

[FONT=&quot]

I have checked the 'Calculations' and they are are set to automatic.

Does anyone have any ideas?[/FONT]

 

[SpillerBD]

The referenced Workbook is Open?
use something similiar to

=A3=[COLOR=#000000][FONT='inherit']'[Sheet1.xlsx]sheet1'!A22[/FONT][/COLOR]

to force a comparison of what you expect to match but apparently isn't.