#value!

[Silo]

Hello Everyone

I have created several work books that a linked to another. Every time I open one of these work books and click OK to allow the link, all the cells that are linked to the other work book result in #VALUE!

I then need to open the actual source or linked work book and all the values get corrected.

I’m sure this is some oversight on my part, how do I correct this?

Thanks

 

[Weaver]

Some formulas don't work on closed workbooks. What formula is causing the problem?

 

[Silo]

The links are mustly Lookup formulas and Pivot Tables that are updated from an external workbook.

There is some code but those are for removing and sorting the data when it comes in and aren't automatic, I run those as needed

Don't know if this matter, all files are located in the sames folder

 

[jasonb75]

Just found this on excelforum

Functions that either return range references or require particular arguments to be range references don’t work with closed workbooks:
OFFSET 1st argument must be a range reference, and it returns a range reference. Excel evaluates external references into closed workbooks as either scalar values or arrays, not as range references in Excel's strict working definition of 'range'.
INDIRECT Always returns a range reference.
RANK 2nd argument must be a range reference, no matter what online help says.
COUNTIF 1st argument must be a range reference.
SUMIF 1st and optional 3rd arguments must be range references.
CELL Optional 2nd argument must be a range reference.

There are probably other functions that this applies to.

 

[Silo]

So are you saying there's no way to keep this from happening? And that I must alway have the source Work Book open?

 

[jasonb75]

It depends on the formula cuasing the problem, and if there is a suitable alternative.

A quick search indicates that =COUNTIF(range,criteria) will fail where the range is in a closed book but {=SUM(IF(range=criteria,1,0))} would work, as would =SUMPRODUCT(--(range=criteria)) 3 different formula that will all give the same results.

 

[Silo]

Jason

This is the formula that I'm using find specfic names based by location, when the source workbook is closed I get the #value! error.

I have no idea how to modify this. I like to be able to use the report even when the source workbook is closed

=IF(ROWS(A$6:A6)<=C$2,INDEX('[T5 MTD Dash Board Report Builder.xls]Territory List'!C$2:C$96,SMALL(IF('[T5 MTD Dash Board Report Builder.xls]Territory List'!B$2:B$96=C$1,ROW('[T5 MTD Dash Board Report Builder.xls]Territory List'!C$2:C$96)-ROW(C$2)+1),ROWS(A$6:A6))),"")

 

[jasonb75]

I don't think that there is an alternative method for INDEX formula, maybe a hidden sheet in the open book holding a copy of the Territory List sheet would be the easiest way, use a formula like

='[T5 MTD Dash Board Report Builder.xls]Territory List'!C2

copied down and accross from C2 in the hidden sheet so that it copies all data from the original, then reference that sheet in your index formula.

 

[Silo]

That's the same conclusion I had, I actually did that, it worked although it did make the file bigger.

Thanks for all the help