Dynamic named range reference from external workbook

[vertblancrouge]

Hi

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

 

[JLatham]

Let's try this experiment - first time I've tried it, and it appears to be
working with the two books open (haven't tried closing the book with the
dynamic range in it yet).

First, some explanation. The ROW() function will give you the first row
number used of a table/array, the COLUMN() does same for first column number
of a table/array. The ROWS() and COLUMNS() function gives you the number of
rows and columns of the array/table.

Now, things would be straight forward if your table started at A1, but that
isn't always the case, so we have to adjust for the table starting in a
column other than A and a row other than 1. This formula does that.

The book with the dynamic range is named Book1 and I named my range MyTable.
Here's the formula to put the address of that table into a cell in your
other workbook:

="Book1!" & ADDRESS(ROW(Book1!MyTable),COLUMN(Book1!MyTable)) & ":" &
ADDRESS(ROWS(Book1!MyTable)+(ROW(Book1!MyTable)-1),COLUMNS(Book1!MyTable)+(COLUMN(Book1!MyTable)-1))

Now, with that formula in a cell you have something that looks like
Book1!$A$4:$D$9
which happens to be exactly where MyTable resides on a sheet in Book1.
I have that formula in cell A2 in Sheet1 of my test workbook, so my
VLOOKUP() which is on another sheet in the test workbook looks like this:
=VLOOKUP(A2,INDIRECT(Sheet1!A2),4,FALSE)

Hope I didn't confuse things too much. The key is to use that first formula
to get the address of the table in a format that you can use in the
INDIRECT() part of the VLOOKUP() function.

Hi

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

 

[JLatham]

Well, that answers that question: The basic formula to get the address of
your dynamic table flips to #REF once I close the book with MyTable in it.
So while this will work (in a fashion) to adjust to the location of your
table, you have to have both workbooks open in order for it to do so.