V

#### vertblancrouge

I have 2 workbooks. WBK1, the first, contains a whole bunch of data that is

contained in a dynamic range, let say MASTERLIST. The data is also contained

in Table1 and covers cells A21000

WBK2, my second workbook, needs to get information from that particular

dynamic range. I am currently using VLOOKUP because my second workbook

generates a list of item that are part of the first column in my MASTERLIST.

Let say I need information about the item in cell A1 of WBK2.

=VLOOKUP(A1,[WBK1.xls]Sheet!$A$2:$D$1000,3,FALSE)

This works just fine, but won't cut it in the long run as my dynamic range

will expand through the upcoming weeks and will require me to manually update

the range every time MASTERLIST is expanded.

=VLOOKUP(A1,[WBK1.xls]Sheet!Table,3,FALSE)

Works again, but yet again, not having dynamic referencing.

=VLOOKUP(A1,[WBK1.xls]Sheet!MASTERLIST,3,FALSE)

Do not return the expected value, only #N/A

I managed to go around this by writting the offset formula directly in my

VLOOKUP formula

=VLOOKUP(A1,OFFSET([WBK1.xls]Sheet!$A2,,,COUNTA([WBK1.xls]Sheet!A:A)-1,4),3,FALSE)

I agree that this is doing just fine, but is there a way to refer directly

to the dynamic named range? I mean, my range could evolve eventually by

adding columns, rearanging the date, etc.

What is weird is that when I go inside the formula editor/function agreement

window, all first 3 options show me the correct preview of my data whether I

use the fixed reference, the Table or the Named Range yet only 2 of 3 return

the correct aswer. But when I write the Offset formula directly in my

VLOOKUP, I get "volatile" with no preview but still get the right answer...

I hope I gave you guys enough info! And I use Excel 2007.

Thanks for your ideas on getting this named range refered from another

workbook.

Marc-Andre