#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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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