#value!

Silo

Active Member
Joined
Mar 8, 2004
Messages
447
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 :)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Some formulas don't work on closed workbooks. What formula is causing the problem?
 

Silo

Active Member
Joined
Mar 8, 2004
Messages
447
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,791
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 8, 2004
Messages
447

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
14,791
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Silo

Active Member
Joined
Mar 8, 2004
Messages
447

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
14,791
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 8, 2004
Messages
447
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 :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,443
Members
431,879
Latest member
KiwDaWabbit

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