Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-12-2019, 04:38 AM
Rapidos Rapidos is offline #VALUE! with COUNTIFS that reference other workbooks Windows 10 #VALUE! with COUNTIFS that reference other workbooks Office 2016
Novice
#VALUE! with COUNTIFS that reference other workbooks
 
Join Date: Feb 2019
Posts: 2
Rapidos is on a distinguished road
Default #VALUE! with COUNTIFS that reference other workbooks

I created a worksheet with cells that take value from other workbooks, like this:


Code:
=COUNTIFS('adress\[name.xlsm]TAT'!D:D,"="&A6,'adress\[name.xlsm]TAT'!B:B, "=Timed out Cases")
And I have created a macro for automatically updating the values upon opening the workbook that looks like this:

Code:
Private Sub Workbook_Open()
    Workbooks.Open "name.xlsm", UpdateLinks:=1, ReadOnly:=True
    ActiveWorkbook.Close SaveChanges:=False
End Sub
Yet somehow only half of my values get updated upon opening (I have 12-15 columns for each workbook I access):


wb1 wb2
#VALUE! 3
#VALUE! 5
#VALUE! 9
#VALUE! 0
#VALUE! 1


So I created a separate macro for opening all the workbooks again when I have already opened my report. And what happens now is that the previously updated columns get #value! and the non-updated half updates:

wb1 wb2


4 #VALUE!
4 #VALUE!
5 #VALUE!
6 #VALUE!
7 #VALUE!

Can anybody help me figure out what is going on here? Thanks.
Reply With Quote
  #2  
Old 02-12-2019, 05:26 AM
Marcia's Avatar
Marcia Marcia is offline #VALUE! with COUNTIFS that reference other workbooks Windows 7 32bit #VALUE! with COUNTIFS that reference other workbooks Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

this is not a solution but an interest in this thread. My experience with formulae that are linked with other workbooks is that errors or #VALUE appear when the source workbooks are not open. I'll keep track on this so I could copy the correct vba code to open all linked workbooks instead of manually opening them.
Reply With Quote
  #3  
Old 02-12-2019, 06:58 AM
Rapidos Rapidos is offline #VALUE! with COUNTIFS that reference other workbooks Windows 10 #VALUE! with COUNTIFS that reference other workbooks Office 2016
Novice
#VALUE! with COUNTIFS that reference other workbooks
 
Join Date: Feb 2019
Posts: 2
Rapidos is on a distinguished road
Default

Quote:
Originally Posted by Marcia View Post
this is not a solution but an interest in this thread. My experience with formulae that are linked with other workbooks is that errors or #VALUE appear when the source workbooks are not open. I'll keep track on this so I could copy the correct vba code to open all linked workbooks instead of manually opening them.
My vba code is opening all the files in read only mode upon opening. Feel free to use it, just copy the middle contents for each workbook and put it inside Workbook () that you can choose in VBA on the left.
Reply With Quote
  #4  
Old 02-12-2019, 08:31 AM
xor xor is offline #VALUE! with COUNTIFS that reference other workbooks Windows 10 #VALUE! with COUNTIFS that reference other workbooks Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Use SUMPRODUCT instead of COUNTIFS.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
count if or countifs how do I do it? piper7971 Excel 6 06-11-2015 12:48 PM
Office 2008 workbooks into Office 2011 workbooks nfotx Excel 0 12-07-2014 04:22 PM
COUNTIFS Help Needed OTPM Excel 2 04-09-2014 08:32 AM
#VALUE! with COUNTIFS that reference other workbooks Countifs and Sumproduct Algo Excel 6 11-13-2012 07:44 AM
countifs? sonyaturpin Excel 1 05-23-2012 08:29 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:19 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft