VLOOKUP problem

[EdMac]

I am in the process of developing a spreadsheet that pulls values through from other workbooks using VLOOKUP.

I have done this in the past and it works fine irrespective of whether the target workbook is open or not.

On this particular workbook, which uses INDIRECT to generate the target, the VLOOKUP fails if the target workbook is not open giving a REF! error.

I have also tried substituting the INDIRECT function for a direct call and get an N/A error.

I need this to work without the target workbook being open - any ideas why this not working in this instance?

Ed

[Carim]

Hi,

Working with closed workbooks can only be solved in 3 ways ...
Take a look at the various explanations ...
http://www.dailydoseofexcel.com/arch...sed-workbooks/

[oldchippy]

See previous post from Carim

http://www.excelforum.com/showthread...losed+workbook

[EdMac]

Thanks for that, Carim and Old Chippy.

I have tried the 'Pull' function Carim pointed me to. Being a bit of a VBA virgin I'm not sure why this hasn't worked either - I have pasted it into the sheet tab but I get a #Name? error

=IF(VLOOKUP(B5,pull($D$1),5,FALSE)="yes","Yes","No")

(D1 contains the full path and file name) and is derived from another vlookup.

I don't understand why a direct reference i.e full path isn't working though. This is the method I have used in the past and it has been fine.

Ed

[EdMac]

Thanks for the attachment, Carim, I seem to have got it all sorted now.

I think my situation was complicated by IT network problems this end.

Regards,

Ed

[Carim]

Glad your problem is fixed

Thanks for the feedback