Formula returning NA unless source is open

[goofy78270]

I have been working on the following for about a week, with no luck. before I get a concussion from banging my head against the desk, I thought I would post the issue here.

I am currently pulling in all cells from various worksheets, into a specific tabs in a workbook. Each source is pulled into a seperate tab such as Workbook A is pulled into tab Company A using the following formula which works fine.

"Path[Workbook.xls]Worksheet!ReferenceCell"
 
Note:  This is done due to the use of indirect in the next formula and its
inability to work on closed workbooks.

Within a summary tab, I am then pulling in specific fields from each of the aforementioned worksheets using the following formula

=IF(OR(M$220="",M$218=""),"",IF(ISNA(VLOOKUP($B223,INDIRECT
("'"&M$217&"'!"&M$220),COLUMNS(INDIRECT(M$220)),FALSE)),"",VLOOKUP
($B223,INDIRECT("'"&M$217&"'!"&M$220),COLUMNS(INDIRECT
(M$220)),FALSE)+M$222))
 
M217 - Worksheet Name
M218 - Worksheet Date
M220 - Worksheet Range
M222 - Additional Fee
B223 - Lookup Value

The issue is that one company out of 14 pulls into this workbook (using the first code) just fine with all values visible, but ONLY when the source file is open. Even if I manually update the link, the values do not change.

Is there a setting or something to check, maybe in the source workbook? I have ensured that automatic updates for links is on and there are no macros in the source workbook.

I am unsure what other information you may need, but if you let me know, I will be sure to submit whatever is needed.

 

[Derek Brown]

This may help:
http://www.ozgrid.com/forum/showthread.php?t=44975&page=1

 

[goofy78270]

Maybe I was unclear, but my issue is not with the indirect function, my issue is with the simple formula to pull data from one workbook into another.

The following formula works for 13 out of 14 workbooks, but one will NOT update unless it is open.

Cell A1 = Formula("Path[Workbook.xls]Worksheet!A1")

The use of the hijacked indirect function is not an option as my company is not inclined to add this add-in to all users that update this workbook.