VLOOKUP doesn't show values from closed workbook

imfreakingout

New Member
Joined
Sep 27, 2006
Messages
4
This is the first time I've used the VLOOKUP function. After playing with it for a while to make sure I understood it, I created 2 workbooks. One is my master information with daily information on separate worksheets by day. The other is a file that compiles the daily information into monthly totals based on a key.

I can get the information to populate correctly if both workbooks are open. However, if the "master" workbook is closed then the other workbook with the VLOOKUP functions displays "VALUE?" errors for every cell with that function.

How can I get the secondary workbook(s) to read the information from the closed "master"?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
VLOOKUP works to closed workbooks, so that is not the root cause of your problem. Can you post the formula you are using and a bit more detail or snapshots using Colo's HTML Maker (see link on Forum Page)
 

imfreakingout

New Member
Joined
Sep 27, 2006
Messages
4
Each cell has a slightly different formula since they pull different data. However, the basic formula is:

=IF(COUNTIF('\\Phxfs01\city\Supervisors\Mark\\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,A2), VLOOKUP(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,2,FALSE), "")
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
I think the problem is in the "Countif" Function: when I try to use a range on a closed workbook as the range to be tested it returns a "#Value" error. Since you are using this to prevent an error in the value returned by the Vlookup function, perhaps you should use a different formulation, like:
Code:
=if(iserror(VLOOKUP(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,2,FALSE), "",VLOOKUP(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,2,FALSE))
(I'm not sure if I got the brackets right - I was just cutting & pasting...)
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

I agree with Dean that COUNTIF will give an error when the range is in a closed workbook. But another point I noticed is that your original COUNTIF will count all incidents of A2 in the entire range B5:AE200, but the following VLOOKUP will only return an answer for the first occurence of A2 in column B (the first column of the lookup range) and the answer returned will be whatever is in column C (the second column of the lookup range) on that row. So your IF(COUNTIF .... will be true if it finds A2 in say cell AB195 thereby triggering the VLOOKUP but then no A2 will be found in column B so the result will be #VALUE.

If what you are trying to do is first check for the existence of A2 in the first column of your lookup range before executing the VLOOKUP then Deans solution will work or your could also try
Code:
=IF(ISNA(MATCH(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$B$200)),"",VLOOKUP(A2,'\\Phxfs01\city\Supervisors\Mark\[Team Stats Daily 2006 TEST.xls]1'!$B$5:$AE$200,2,FALSE)
I did not test this, but I think the brackets etc are right
 

imfreakingout

New Member
Joined
Sep 27, 2006
Messages
4
We're getting close. Thanks for your help. Here's where I'm at now. When I enter the value 6201 in A2 (which is the value I've been using for testing), everything works fine. When I change the value to something else (e.g. 6233), the data is populated, but it also returns #N/A errors where data wasn't found. This only happens on A2 values other than the original 6201 I have used for testing when I created the worksheet.

Here is a copy of the formula when A2 = 6203:
=IF(ISNA(MATCH(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$5:$B$900,A2)),"",VLOOKUP(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$5:$AE$900,2,FALSE))

Here is a copy of the formula from the same cell when A2 = 6233:
=IF(ISNA(MATCH(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$5:$B$900,A2)),"",VLOOKUP(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$5:$AE$900,2,FALSE))

The file path mentioned in my original posting is not shown here because I had both workbooks opened when I copied the formulas.

I cannot use Colo's html maker here. However, I did create jpegs of them if that would help.

Thanks again!
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
try: =IF(ISNA(MATCH(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$2:$B$900,0)),"",VLOOKUP(A2,'[FIT Phone Stats 2006 Sept.xls]2'!$B$2:$AE$900,2,0))
 

imfreakingout

New Member
Joined
Sep 27, 2006
Messages
4
THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU.....

Saying thanks doesn't seem like enough. I stared at the formula for hours and finally saw a tiny flaw, changed it and now everything works perfectly. Thank you so much to each of you who helped me with this problem!
 

Forum statistics

Threads
1,137,367
Messages
5,681,075
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top