+ Reply to Thread
Results 1 to 2 of 2

#REFs eventually appear -- why?

  1. #1
    Registered User
    Join Date
    Montreal, Quebec, Canada
    MS-Off Ver

    #REFs eventually appear -- why?

    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.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    Tysons Corner, VA, USA
    MS-Off Ver

    Re: #REFs eventually appear -- why?

    I have looked at both workbooks and there isn't enough information to diagnose your problem. But I can tell you that when references in a formula are replaced with #REF! it's almost always because the cell, row, or column referred to has been deleted.

    However, in your case the formula didn't just get #REF! errors, it changed dramatically--it's a different formula.

    Another thing you didn't mention is that your original formula is an array formula, and the "after" version is not. The original is actually

    {=IF(OR((E4:OFFSET(E4, $B$2-1, 0))<TODAY()-365),"Old","")}

    (This was copied out of your example, though the formula in your post refers to $B$6.)

    All of this points to another formula getting copied and pasted into E1. Can you describe what is happening "over the course of a working with the spreadsheets"? Do you have any VBA? Do you have a formula like the second one somewhere (do a Find on something like ="N" to check)?

    This doesn't have anything to do with your autofilter.
    Last edited by 6StringJazzer; 03-12-2010 at 02:37 PM.
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1