vlookup name range separate workbook?

[exceliz]

Hi

is it possible to have a vlookup pointing at different spreadsheet and within the lookup using a named range?

thanks
any help is appreciated

i have this formula and its coming up as an error

=VLOOKUP($A$3,[Worksheets.xls]medians,3,FALSE)

 

[taigovinda]

Where your named range is called 'medians' and is housed in the workbook called 'Worksheets' I would expect the syntax to look like this:

=VLOOKUP($A$3,Worksheets!medians,3,FALSE)

How is 'medians' defined? If it is dynamically defined and you are trying to evaluate the formula while 'Worksheets' is closed, that could be the problem...

 

[exceliz]

thats perfect THANKS
when i linked via the formula it put the worksheet in [] so i think this was the problem i have just put a ! like in your formula shown and it works perfect whent the sheet is open!

would there be a way with the sheet closed?

Thanks for your help

 

[taigovinda]

I think this remains the question:

How is 'medians' defined? If it is dynamically defined and you are trying to evaluate the formula while 'Worksheets' is closed, that could be the problem...

 

[exceliz]

i just created a define name range called medians?
im not sure what you mean?

sorry

 

[taigovinda]

So the name refers to a fixed range, the name's definition is something like this?

=Sheet1!$A$1:$B$10

If the range is fixed, I would expect it to work when the other book is closed...

When I close my other workbook, this:

=VLOOKUP(A1,zzzzzzztesttest.xls!my_range,2,0)

...automatically changes to this:

=VLOOKUP(A1,'C:\Documents and Settings\Tai\My Documents\temp\zzzzzzztesttest.xls'!my_range,2,0)

...and continues to work when I recalculate.

 

[exceliz]

arhh thank you, and it asks you to update it when opening!

 

[taigovinda]

If you want to do it automatically, I think you can change a setting to not get that prompt...

From http://support.microsoft.com/kb/826921 ...


To suppress the message and to automatically update the links when you open a workbook:

In Excel 2002 or in Excel 2003

1. On the Tools menu, click Options, and then click the Edit tab.
2. Click to clear the Ask to update automatic links check box.

In Excel 2007:

1. Click Microsoft Office Button, and then click Excel Options.
2. Click Advanced.
3. Under General, click to clear the Ask to update automatic links check box.