#1
|
|||
|
|||
#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") Code:
Private Sub Workbook_Open() Workbooks.Open "name.xlsm", UpdateLinks:=1, ReadOnly:=True ActiveWorkbook.Close SaveChanges:=False End Sub 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. |
#2
|
||||
|
||||
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.
|
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
Use SUMPRODUCT instead of COUNTIFS.
|
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 |
Countifs and Sumproduct | Algo | Excel | 6 | 11-13-2012 07:44 AM |
countifs? | sonyaturpin | Excel | 1 | 05-23-2012 08:29 AM |