Vlookup from url

Hey sixthsense, thanks for the suggestions,just trying to find the least troublesome solutions for the end users. i've got more questions.

any idea why vlookup can sometimes update from a closed workbook but sometimes it cant? i.e. when i make changes to the lookup_value, it shows a #NA on the resultant cell, but i can just revert this change and get back the result. but sometimes it shows a #ref, and there's no reverting the situation. both situations are with the source workbook closed.