#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 :)
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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
10,687
Office Version
2019
Platform
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
10,687
Office Version
2019
Platform
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
10,687
Office Version
2019
Platform
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 :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,616
Messages
5,512,428
Members
408,894
Latest member
Sara Russell

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top