VLOOKUP Looking In Different Workbook Confusion

[Guest]

Good day,

I have a source workbook (ITEM_DBASE) that has about 23K lines of data; I
want to use VLOOKUP in a second workbook (COST) to grab data from ITEM_DBASE.

I have tried directly referencing the ITEM_DBASE array
(...path\[file]!UL:BR), as well as naming the array in ITEM_DBASE and
referencing the name in the function, as well as naming the array in COST and
then referencing *that* name in the function. Varying degrees of elegance,
with no difference in frustration.

It has been inferred here that the source book needn't be open for the
VLOOKUP function to work. I seem to recall reading in the Help some time ago
that the source book had to be open, and this is supported by when I have
trouble (ITEM_DBASE closed) and when I don't (ITEM_DBASE open). When I close
ITEM_DBASE I get the 'Excel cannot complete this task with available
resources' error and then the linking stops working.

So, I wonder if there is some subtlety that I'm not exploiting that allows
my LOOKUP functions to correctly acess a table_array in a closed workbook? It
seems silly to want the source workbook closed, but that's how the boss wants
it....

Thanks in advance for your time.

 

[Marc]

The easiest way to make sure your link/formula is correct, is to create the
formula while the other file is open, and when you get to the second
parameter in the lookup, just navigate to the other file, select the
appropriate range, press "," to continue the formula. You will probably have
to manually navigate back to your original file to continue the formula.

Then, when you close the reference file, the link in the lookup formula will
change to the full path...

 

[Marc]

Upon re-reading your post, it seems your formula is correct, as it appears
to work when the other file is open.

I just tested the scenario, and I have no problem refreshing a VLOOKUP with
the reference file being closed... so that's why I suspect your formula
isn't exactly correct in its full path name to the reference file...

If you have a ton of lookups, maybe you closed the reference file before
Excel completely finished all the lookups and any possible recalcs.

I've also seen that error, but usually completely closing down Excel, then
re-starting cleans it up...

The easiest way to make sure your link/formula is correct, is to create the
formula while the other file is open, and when you get to the second
parameter in the lookup, just navigate to the other file, select the
appropriate range, press "," to continue the formula. You will probably have
to manually navigate back to your original file to continue the formula.

Then, when you close the reference file, the link in the lookup formula will
change to the full path...

Good day,

I have a source workbook (ITEM_DBASE) that has about 23K lines of data; I
want to use VLOOKUP in a second workbook (COST) to grab data from ITEM_DBASE.

I have tried directly referencing the ITEM_DBASE array
(...path\[file]!UL:BR), as well as naming the array in ITEM_DBASE and
referencing the name in the function, as well as naming the array in

COST

and
then referencing *that* name in the function. Varying degrees of elegance,
with no difference in frustration.

It has been inferred here that the source book needn't be open for the
VLOOKUP function to work. I seem to recall reading in the Help some time ago
that the source book had to be open, and this is supported by when I have
trouble (ITEM_DBASE closed) and when I don't (ITEM_DBASE open). When I close
ITEM_DBASE I get the 'Excel cannot complete this task with available
resources' error and then the linking stops working.

So, I wonder if there is some subtlety that I'm not exploiting that allows
my LOOKUP functions to correctly acess a table_array in a closed

workbook?

It
seems silly to want the source workbook closed, but that's how the boss wants
it....

Thanks in advance for your time.

 

[Guest]

Hi Marc,

Thanks for your quick replies.

This is very confusing to me, because if I enter in an item I know to exist
in ITEM_DBASE, VLOOKUP returns #N/A; when I open the source book, however,
the function returns the correct data.

Using the approach you and other elsewhere have suggested yields:
=VLOOKUP(C6,ITEM_DBASE.xls!$C$11:$G$23309,3,FALSE) when ITEM_DBASE is open,
=VLOOKUP(C6,'C:\Documents and Settings\...\My
Documents\Reference\[ITEM_DBASE.xls]ITEM_DBASE'!$C$11:$G$23309,3,FALSE) when
it is closed. I did no massaging, or manual entry, of this function in the
workbook, though I did remove some nonrelevant data for this post.

It's interesting that the file name is listed twice once I close it...at
least I *think* it's interesting.

Any thoughts you might have to share would be most appreciated.

Upon re-reading your post, it seems your formula is correct, as it appears
to work when the other file is open.

I just tested the scenario, and I have no problem refreshing a VLOOKUP with
the reference file being closed... so that's why I suspect your formula
isn't exactly correct in its full path name to the reference file...

If you have a ton of lookups, maybe you closed the reference file before
Excel completely finished all the lookups and any possible recalcs.

I've also seen that error, but usually completely closing down Excel, then
re-starting cleans it up...

The easiest way to make sure your link/formula is correct, is to create the
formula while the other file is open, and when you get to the second
parameter in the lookup, just navigate to the other file, select the
appropriate range, press "," to continue the formula. You will probably have
to manually navigate back to your original file to continue the formula.

Then, when you close the reference file, the link in the lookup formula will
change to the full path...

Good day,

I have a source workbook (ITEM_DBASE) that has about 23K lines of data; I
want to use VLOOKUP in a second workbook (COST) to grab data from ITEM_DBASE.

I have tried directly referencing the ITEM_DBASE array
(...path\[file]!UL:BR), as well as naming the array in ITEM_DBASE and
referencing the name in the function, as well as naming the array in

COST

and
then referencing *that* name in the function. Varying degrees of elegance,
with no difference in frustration.

It has been inferred here that the source book needn't be open for the
VLOOKUP function to work. I seem to recall reading in the Help some time ago
that the source book had to be open, and this is supported by when I have
trouble (ITEM_DBASE closed) and when I don't (ITEM_DBASE open). When I close
ITEM_DBASE I get the 'Excel cannot complete this task with available
resources' error and then the linking stops working.

So, I wonder if there is some subtlety that I'm not exploiting that allows
my LOOKUP functions to correctly acess a table_array in a closed

workbook?

It
seems silly to want the source workbook closed, but that's how the boss wants
it....

Thanks in advance for your time.

 

[Guest]

**For some reason my username has changed and I cannot change it back;
leaning_forward == .plaid**

So, my question is: what trick must I employ in order to get Excel to read a
range of data from a closed workbook? Reading one cell is no problem, but
trying to read a range generates a 'resource' error as described below.

Thanks in advance for your time,

Hi Marc,

Thanks for your quick replies.

This is very confusing to me, because if I enter in an item I know to exist
in ITEM_DBASE, VLOOKUP returns #N/A; when I open the source book, however,
the function returns the correct data.

Using the approach you and other elsewhere have suggested yields:
=VLOOKUP(C6,ITEM_DBASE.xls!$C$11:$G$23309,3,FALSE) when ITEM_DBASE is open,
=VLOOKUP(C6,'C:\Documents and Settings\...\My
Documents\Reference\[ITEM_DBASE.xls]ITEM_DBASE'!$C$11:$G$23309,3,FALSE) when
it is closed. I did no massaging, or manual entry, of this function in the
workbook, though I did remove some nonrelevant data for this post.

It's interesting that the file name is listed twice once I close it...at
least I *think* it's interesting.

Any thoughts you might have to share would be most appreciated.

Upon re-reading your post, it seems your formula is correct, as it appears
to work when the other file is open.

I just tested the scenario, and I have no problem refreshing a VLOOKUP with
the reference file being closed... so that's why I suspect your formula
isn't exactly correct in its full path name to the reference file...

If you have a ton of lookups, maybe you closed the reference file before
Excel completely finished all the lookups and any possible recalcs.

I've also seen that error, but usually completely closing down Excel, then
re-starting cleans it up...

The easiest way to make sure your link/formula is correct, is to create the
formula while the other file is open, and when you get to the second
parameter in the lookup, just navigate to the other file, select the
appropriate range, press "," to continue the formula. You will probably have
to manually navigate back to your original file to continue the formula.

Then, when you close the reference file, the link in the lookup formula will
change to the full path...

message Good day,

I have a source workbook (ITEM_DBASE) that has about 23K lines of data; I
want to use VLOOKUP in a second workbook (COST) to grab data from
ITEM_DBASE.

I have tried directly referencing the ITEM_DBASE array
(...path\[file]!UL:BR), as well as naming the array in ITEM_DBASE and
referencing the name in the function, as well as naming the array in COST
and
then referencing *that* name in the function. Varying degrees of elegance,
with no difference in frustration.

It has been inferred here that the source book needn't be open for the
VLOOKUP function to work. I seem to recall reading in the Help some time
ago
that the source book had to be open, and this is supported by when I have
trouble (ITEM_DBASE closed) and when I don't (ITEM_DBASE open). When I
close
ITEM_DBASE I get the 'Excel cannot complete this task with available
resources' error and then the linking stops working.

So, I wonder if there is some subtlety that I'm not exploiting that allows
my LOOKUP functions to correctly acess a table_array in a closed workbook?
It
seems silly to want the source workbook closed, but that's how the boss
wants
it....

Thanks in advance for your time.

 

[Marc]

Ok, played around some more, and I can reproduce part of you problem.

First, about the file name being listed twice--I think it's just the "file
name"/"tab name", where they both are the same...

I created a blank sheet with 4 VLOOKUPS into a reference file that contains
22,282 rows, I changed my key lookup item a few times, and it worked fine.
When I closed the reference file I got the same "resources" error as you,
but it doesn't affect anything. My 4 VLOOKUP formulas still work fine...

I saved the file and exited with no problems.

Reopening the file, if I click "Update" when the link box question pops up,
I get the error, but the links still work. If I click "Don't Update" (which
I always do in practice), it opens with no error messages, and the formulas
still work.

Can't explain why your lookup works with the reference file open, but
doesn't work when it's closed.

Otherwise, for me anyway, I can ignore the resources error message with no
ill effect...

What about some setup options, like recalc (manual/automatic), "update
remote references", and "save external link values".

The last 2 items are checked for me, but I haven't researched what they
actually do... maybe something to look at?

**For some reason my username has changed and I cannot change it back;
leaning_forward == .plaid**

So, my question is: what trick must I employ in order to get Excel to read a
range of data from a closed workbook? Reading one cell is no problem, but
trying to read a range generates a 'resource' error as described below.

Thanks in advance for your time,

Hi Marc,

Thanks for your quick replies.

This is very confusing to me, because if I enter in an item I know to exist
in ITEM_DBASE, VLOOKUP returns #N/A; when I open the source book, however,
the function returns the correct data.

Using the approach you and other elsewhere have suggested yields:
=VLOOKUP(C6,ITEM_DBASE.xls!$C$11:$G$23309,3,FALSE) when ITEM_DBASE is open,
=VLOOKUP(C6,'C:\Documents and Settings\...\My
Documents\Reference\[ITEM_DBASE.xls]ITEM_DBASE'!$C$11:$G$23309,3,FALSE) when
it is closed. I did no massaging, or manual entry, of this function in the
workbook, though I did remove some nonrelevant data for this post.

It's interesting that the file name is listed twice once I close it...at
least I *think* it's interesting.

Any thoughts you might have to share would be most appreciated.

Upon re-reading your post, it seems your formula is correct, as it appears
to work when the other file is open.

I just tested the scenario, and I have no problem refreshing a VLOOKUP with
the reference file being closed... so that's why I suspect your formula
isn't exactly correct in its full path name to the reference file...

If you have a ton of lookups, maybe you closed the reference file before
Excel completely finished all the lookups and any possible recalcs.

I've also seen that error, but usually completely closing down Excel, then
re-starting cleans it up...


The easiest way to make sure your link/formula is correct, is to create
the
formula while the other file is open, and when you get to the second
parameter in the lookup, just navigate to the other file, select the
appropriate range, press "," to continue the formula. You will probably
have
to manually navigate back to your original file to continue the formula.

Then, when you close the reference file, the link in the lookup formula
will
change to the full path...

message Good day,

I have a source workbook (ITEM_DBASE) that has about 23K lines of data;
I
want to use VLOOKUP in a second workbook (COST) to grab data from
ITEM_DBASE.

I have tried directly referencing the ITEM_DBASE array
(...path\[file]!UL:BR), as well as naming the array in ITEM_DBASE and
referencing the name in the function, as well as naming the array in
COST
and
then referencing *that* name in the function. Varying degrees of
elegance,
with no difference in frustration.

It has been inferred here that the source book needn't be open for the
VLOOKUP function to work. I seem to recall reading in the Help some time
ago
that the source book had to be open, and this is supported by when I
have
trouble (ITEM_DBASE closed) and when I don't (ITEM_DBASE open). When I
close
ITEM_DBASE I get the 'Excel cannot complete this task with available
resources' error and then the linking stops working.

So, I wonder if there is some subtlety that I'm not exploiting that
allows
my LOOKUP functions to correctly acess a table_array in a closed
workbook?
It
seems silly to want the source workbook closed, but that's how the boss
wants
it....

Thanks in advance for your time.

 

[Marc]

Ok... did a Google search, and have concluded that what we're seeing REALLY
is a systems memory limitation...

I hit the limit at around 18,000 rows in the reference file. With fewer
rows, I don't get any error messages...

However I still think that even though you get the error initially, the
lookups will still work.

Ok, played around some more, and I can reproduce part of you problem.

First, about the file name being listed twice--I think it's just the "file
name"/"tab name", where they both are the same...

I created a blank sheet with 4 VLOOKUPS into a reference file that contains
22,282 rows, I changed my key lookup item a few times, and it worked fine.
When I closed the reference file I got the same "resources" error as you,
but it doesn't affect anything. My 4 VLOOKUP formulas still work fine...

I saved the file and exited with no problems.

Reopening the file, if I click "Update" when the link box question pops up,
I get the error, but the links still work. If I click "Don't Update" (which
I always do in practice), it opens with no error messages, and the formulas
still work.

Can't explain why your lookup works with the reference file open, but
doesn't work when it's closed.

Otherwise, for me anyway, I can ignore the resources error message with no
ill effect...

What about some setup options, like recalc (manual/automatic), "update
remote references", and "save external link values".

The last 2 items are checked for me, but I haven't researched what they
actually do... maybe something to look at?

**For some reason my username has changed and I cannot change it back;
leaning_forward == .plaid**

So, my question is: what trick must I employ in order to get Excel to

read

a
range of data from a closed workbook? Reading one cell is no problem, but
trying to read a range generates a 'resource' error as described below.

Thanks in advance for your time,

Documents\Reference\[ITEM_DBASE.xls]ITEM_DBASE'!$C$11:$G$23309,3,FALSE)

when

VLOOKUP

with

Excel,

then

re-starting cleans it up...


The easiest way to make sure your link/formula is correct, is to create
the
formula while the other file is open, and when you get to the second
parameter in the lookup, just navigate to the other file, select the
appropriate range, press "," to continue the formula. You will probably
have
to manually navigate back to your original file to continue the formula.

Then, when you close the reference file, the link in the lookup formula
will
change to the full path...

in

of

data;

I
want to use VLOOKUP in a second workbook (COST) to grab data from
ITEM_DBASE.

I have tried directly referencing the ITEM_DBASE array
(...path\[file]!UL:BR), as well as naming the array in

ITEM_DBASE

and

array

in

for

the

when

I

the

boss