#VALUE! with COUNTIFS that reference other workbooks

Rapidos · #1 02-12-2019, 04:38 AM

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.

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
Countifs and Sumproduct	Algo	Excel	6	11-13-2012 07:44 AM
countifs?	sonyaturpin	Excel	1	05-23-2012 08:29 AM