Vlookup displays REF if linked workbook is open

[crumpets266]

Hi,

I tried to search similar problems and only found one other person with a similar problem, but it was not resolved. I have a Vlookup function in one workbook that is linked to another workbook called Job List 7.xls. When the Job List 7 workbook is closed, the vlookup function works, but as soon as the Job List 7 workbook is open then my vlookup function reveals #ref.
This is my formula =IF(A5="","",VLOOKUP(A5,'\\192.168.10.201\eas\D Drive\Company\EAS Files\MASTER FILES\[JOB LIST 7.xls]Master Job List'!$A$9:$B$4202,2,FALSE))

Is there anyway to make the vlookup function work regardless of whether the Job List is open or not?

Thanks in advance.

PS: if a excel file is to be uploaded please let me know

[:) Sixthsense :)]

It should work Press Alt+E+K and press Update Values.

[crumpets266]

The option of updating the values is masked, the popup table also shows that it is automatically updated? I'n not sure what to do now.

[crumpets266]

I realised my workbook was protected, i unprotected it, selected update value and nothing changes?

[:) Sixthsense :)]

if possible, share some screenshots

[crumpets266]

After.pngBefore.pngJob List.pngAfter.pngBefore.pngJob List.png

[crumpets266]

Sorry, posted it twice, so after is of course when the job list is opened, and before is before the job list is open. i couldnt update the workbooks due to the uploader not allowing macro enables files

[:) Sixthsense :)]

The masterjoblist sheet missing in your formula reference. Pls check it

[crumpets266]

Hi sixthsense,
I'm not sure if I understand what you're saying. It gets replaced by #ref in the formula once the job list is open, but it is in the formula when the job list isn't open. Thats what I'm trying to avoid.

[Izandol]

This will happen if the sheet does not exist in the source workbook. I do not think you may stop this.

[:) Sixthsense :)]

@ Izandol,

Thanks for jumping in

But the OP saying that when he close the workbook it's working fine. When the source workbook is opened then the formula reference is getting affected. Not sure what's happening at OP's end

[Izandol]

Yes this is normal. If source workbook is closed, formula is not affected but if source workbook is open and sheet name is not correct, formula will change to #REF error when workbook with formula is opened.

[crumpets266]

But the sheet name is correct? I never change it

[Izandol]

Are you 100% certain? No spaces at beginning or end? I do not know why this would create error if sheet name is correct.

[crumpets266]

Yes I am sure. I just double checked now. That's why it is so strange. I saw a post as well where someone had a similar problem but it wasn't solved.

[Izandol]

May you post both files for testing? No data required but the formulas must be present.

[crumpets266]

i for some reason cannot upload my xlsx file? i tried last night and this morning. Will carry on trying different file types

[Izandol]

What is size of the file? I think there are limits here.

[:) Sixthsense :)]

Attaching the excel file the limit is 1 MB
You can attach upto 10 MB of file by adding it in zip file.

[crumpets266]

Its only 126kb. Going to try again now

[crumpets266]

I really do not know what to do about this, I even saved it as an xls file and it still doesnt work. The original file was macro enabled and I saved it to xlsx first which didnt work and then xls which also wont work. it uploads fully then produces a red explanation mark. Hav I just started a new forum about how to upload files now!? I've uploaded so many files on here before. Strange

[Izandol]

Do you have account for SkyDrive or Dropbox or other upload site?

[crumpets266]

yes i do. I have skydrive and dropbox

[Izandol]

You may upload there and provide share link here?

[crumpets266]

good idea.
https://www.dropbox.com/sh/9vndql934b2loj9/hr0S0esiXn

[Izandol]

I do not see this REF error after changing the path to the downloaded file. It does not matter if source file is open or closed. I am not sure why you have this problem.

[crumpets266]

Seriously? Iwonder if it has something to do with our NAS system that it has to search the Job List file. Because I can't seem to stop this ref, even when i reenter the vlookup function. Have you ever come across this problem with other people or am I a first?

[Izandol]

I really do not know. Is the same problem apparent if you save the source file as .xlsx instead of .xls?

[crumpets266]

Yes still has same problem. If the Job List is open first, and then i open my timesheet, the ref does not occur. If i open my timesheet and then the Job List, then ref occurs. Wow, really strange now.

[:) Sixthsense :)]

Check and confirm, whether post #6 of the below link solves your problem

http://www.excelforum.com/excel-form...file-open.html

[chrisf78]

I had this problem and it was ruining me.

Found a solution in removing the spaces in the sheet names!

[AndyTheAnimal]

I have been having this exact same issue! Unfortunately I can't get to page 3 of this string, it keeps routing me back to page 2 of 3 :-(