 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
HELP!
So I have a spread sheet which contains decimal values for week periods contained in columns. It looks for values in the cells to the right of it and if there are values in the four preceding weeks or 12 subsequent months show the text "Allocated". If there are no values it shows "No Hours"
=IF(SUBTOTAL(9,INDIRECT("t_Resource_Summary[@["&c_Monday_FourWeeksAgo&"]:["&c_Monday_12MonthsTime&"]]"))>0,"Allocated","No Hours")
Since the beginning of this year it has stopped working. If I change my system date to a date last year it works again.
Does anyone know why this is...?
More importantly - can anyone correct the formula so it will work? I am a simpleton - so the least change solution is preferred!
Thanks,
J
what error is returned? is it REF#?
I think that whatever the names 'c_Monday_FourWeeksAgo' and 'c_Monday_12MonthsTime' are referencing need to be adjusted.
[quote=brakes said]I think that whatever the names 'c_Monday_FourWeeksAgo' and 'c_Monday_12MonthsTime' are referencing need to be adjusted.
+1 they would seem to be worth investigating first. Given that monday four weeks ago is in a different year now then something odd could be happening here.
Yes, brakes, it is a #REF! error.
The items they are referencing are some column headers containing dates. The date is in the format dd_month_yyyy.
Do the data ranges under those column headers contain 2014 dates?
njee20, underneath those column headers you only have values for an amount of hours allocated in that week.
Bump
Since it works if you change the system date, there must be something in the spreadsheet that uses the current date. Search for NOW() and if it's there, see how the result is used.
I think they are named ranges or hidden tabs? Not near a computer. Could it be sql code behind the ranges?