SimonGeoghegan
Board Regular
- Joined
- Nov 5, 2013
- Messages
- 68
Hi All,
I am trying to use a MAXIFS Formula to determine the latest date in a column, and then substract it from today to provide a number of days. I am doing this across multiple files within a folder on my desktop however it shows as "#VALUE!" until the referenced file is opened, at which point it calculates the formula.
I also have another formula which contains COUNTIFS that is experiencing the same problem.
Is there anyway of adapting these formulas so that it will display the number, regardless of whether the underlying file is closed or not?
The formulas I'm using are:
As ever, any help greatly appreciated
I am trying to use a MAXIFS Formula to determine the latest date in a column, and then substract it from today to provide a number of days. I am doing this across multiple files within a folder on my desktop however it shows as "#VALUE!" until the referenced file is opened, at which point it calculates the formula.
I also have another formula which contains COUNTIFS that is experiencing the same problem.
Is there anyway of adapting these formulas so that it will display the number, regardless of whether the underlying file is closed or not?
The formulas I'm using are:
Excel Formula:
=TODAY()-MAXIFS('C:\Users\sgeoghegan1\Desktop\XXXX\[XXXX]ACTION PLAN '!$C:$C,'C:\Users\sgeoghegan1\Desktop\XXXX\[XXXX]ACTION PLAN '!$E:$E,"QAAT",'':\Users\sgeoghegan1\Desktop\XXXX\[XXXX]ACTION PLAN '!$L:$L,"OK")
Excel Formula:
=COUNTIFS('C:\Users\sgeoghegan1\Desktop\XXXX\[XXXX]ACTION PLAN '!$D:$D,"QAAT",'C:\Users\sgeoghegan1\Desktop\XXXX\[XXXX]ACTION PLAN '!$K:$K,"Not OK")/COUNTIF('C:\Users\sgeoghegan1\Desktop\XXXX\[XXXX]ACTION PLAN '!$D:$D,"QAAT")
As ever, any help greatly appreciated