Hi, there.
I have a spreadsheet with many worksheets. Each worksheet represents a checklist, with a column Date Last Reviewed yyyy-mm-dd representing the last time the item in the row was last checked. Just above the top of the column header I put this array formula.
=IF(OR((Y8:OFFSET(Y8, $B$6-1, 0))<TODAY()-365),"Old","")
where $B$6 contains merely a count of the non-blank rows in the checklist =COUNTA(B$8:B$65536). The idea is that the formula will check for dates older than a year, including blanks, and put "Old" in the cell if there is an item that has not be checked in a year, if at all.
What happens is that, over the course of a working with the spreadsheets, these formulas change to
=IF(OR(#REF!:OFFSET(#REF!, $B$6-1, 0)="N"),"N","").
and I don't understand why. I use autofilters a lot to zero in on data, and I suspect that that may have something to do with it. I've tried searching the forums, but haven't found anything.
Can anybody shed some light?
I attach an stripped-down example before & after.
Bookmarks