Vlookup Function

[tones]

Hi, Using the vlookup function from one spreadhseet to another.
The 1st spreassheet, where the vlookup is, will be used for calculations while the 1nd spreadsheet is being used to source data from am external database eg Factset.
Issue I am having is that when I update data or amend the data I am searchig for, this does not update dynamically through the Vlookup.
As a result, I am trying to use the INDIRECT Function in the vlookup with the following formula

=VLOOKUP(A6,INDIRECT("([Factset_Data.xlsm]Dublin!$A:$D"),4,FALSE)

File name = Factset_Data
Tab Name = Dublin
I ahve the location of the file I am looking to typed in Cell A1 (as per instructions).
This however is not working, returning the following response in the cell - #REF!

Can someone direct me as to what is incorrect? or any potential solutions/help?
Thanks in advance.

[underpantsdance]

The vlookup function should be reasonably dynamic, not sure how frequently it will update calculations though. Have you looked at your excel calculation settings? A recent update switched mine to manual for some reason which caused me some fun!
Without looking into your issue to much I know that the Indirect function will only work with both files open at the same time. I'm also not sure that the way you've used it is the the right use for that function, it's generally used to turn a string of text into a reference, which you'd need if the reference range was likely to change on the sheet. PM me if you want and I can take a look at an example or something

Edit...

Actually on reflection I've spotted an issue in your formula that will be causing you to return a #REF error

You will need to have an indirect function for each side of the reference range, and in addition you will need to lock the range to a defined range within columns A to D ie. (Replace A1 & D9999 with whatever make sense for your sheet)
=VLOOKUP(A6,INDIRECT("[Factset_Data.xlsm]Dublin!$A$1",TRUE):INDIRECT("[Factset_Data.xlsm]Dublin!$D$9999",TRUE),4,FALSE)

I'm still not sure if this will resolve your issue I can't help feeling that it will add a unnecessary layer of complexity without fixing the cause of you issue with dynamic updating

[tones]

The vlookup function should be reasonably dynamic, not sure how frequently it will update calculations though. Have you looked at your excel calculation settings? A recent update switched mine to manual for some reason which caused me some fun!
Without looking into your issue to much I know that the Indirect function will only work with both files open at the same time. I'm also not sure that the way you've used it is the the right use for that function, it's generally used to turn a string of text into a reference, which you'd need if the reference range was likely to change on the sheet. PM me if you want and I can take a look at an example or something

Edit...

Actually on reflection I've spotted an issue in your formula that will be causing you to return a #REF error

You will need to have an indirect function for each side of the reference range, and in addition you will need to lock the range to a defined range within columns A to D ie. (Replace A1 & D9999 with whatever make sense for your sheet)
=VLOOKUP(A6,INDIRECT("[Factset_Data.xlsm]Dublin!$A$1",TRUE):INDIRECT("[Factset_Data.xlsm]Dublin!$D$9999",TRUE),4,FALSE)

I'm still not sure if this will resolve your issue I can't help feeling that it will add a unnecessary layer of complexity without fixing the cause of you issue with dynamic updating

Yeah have Culcations set to Automatic.....hate that function in 2010...

Got this to work in the end using the following;
=VLOOKUP(A6,INDIRECT("'"&A1&"'!$A:$B"),2,FALSE)
A1 contains the location of the file I am looking into, and then $A:$B" are the columns I am looking at.
Works perfectly, updating dynamically! Awesome!
Thanks.