recognize #REF! particles of invalidated references, tdf#86575 follow-up

... so they result in an invalid reference again producing a #REF! error
instead of a bad string producing #NAME? error.

This way we can handle the invalid #REF!.A1 and similar references that
were wrongly written to ODFF between 2013 and 2016 until 5.1.4

As a benefit, this is now also the case in UI, e.g. when recompiling
changed names with already invalidated references.

Change-Id: I117d709f594b7c37d899528a51220c1855b7817d

4 files changed, 264 insertions, 91 deletions

diff --git a/sc/inc/address.hxx b/sc/inc/address.hxx
index 218f3462132c..f02f064b7a38 100644
--- a/sc/inc/address.hxx
+++ b/sc/inc/address.hxx
@@ -320,7 +320,8 @@ public:
                     const Details& rDetails = detailsOOOa1,
                     ExternalInfo* pExtInfo = nullptr,
                     const css::uno::Sequence<css::sheet::ExternalLinkInfo>* pExternalLinks = nullptr,
-                    sal_Int32* pSheetEndPos = nullptr );
+                    sal_Int32* pSheetEndPos = nullptr,
+                    const OUString* pErrRef = nullptr );
 
     SC_DLLPUBLIC void Format( OStringBuffer& r, ScRefFlags nFlags = ScRefFlags::ZERO,
                                   const ScDocument* pDocument = nullptr,
@@ -504,7 +505,8 @@ public:
     SC_DLLPUBLIC ScRefFlags Parse( const OUString&, ScDocument* = nullptr,
                                    const ScAddress::Details& rDetails = ScAddress::detailsOOOa1,
                                    ScAddress::ExternalInfo* pExtInfo = nullptr,
-                                   const css::uno::Sequence<css::sheet::ExternalLinkInfo>* pExternalLinks = nullptr );
+                                   const css::uno::Sequence<css::sheet::ExternalLinkInfo>* pExternalLinks = nullptr,
+                                   const OUString* pErrRef = nullptr );
 
     SC_DLLPUBLIC ScRefFlags ParseAny( const OUString&, ScDocument* = nullptr,
                                       const ScAddress::Details& rDetails = ScAddress::detailsOOOa1 );
diff --git a/sc/inc/compiler.hxx b/sc/inc/compiler.hxx
index d8f5e3e3cede..abc41e5a4407 100644
--- a/sc/inc/compiler.hxx
+++ b/sc/inc/compiler.hxx
@@ -312,10 +312,11 @@ private:
     bool IsOpCode( const OUString&, bool bInArray );
     bool IsOpCode2( const OUString& );
     bool IsString();
-    bool IsReference( const OUString& );
-    bool IsSingleReference( const OUString& );
-    bool IsPredetectedReference(const OUString&);
-    bool IsDoubleReference( const OUString& );
+    bool IsReference( const OUString& rSymbol, const OUString* pErrRef = nullptr );
+    bool IsSingleReference( const OUString& rSymbol, const OUString* pErrRef = nullptr );
+    bool IsDoubleReference( const OUString& rSymbol, const OUString* pErrRef = nullptr );
+    bool IsPredetectedReference( const OUString& rSymbol );
+    bool IsPredetectedErrRefReference( const OUString& rName, const OUString* pErrRef );
     bool IsMacro( const OUString& );
     bool IsNamedRange( const OUString& );
     bool IsExternalNamedRange( const OUString& rSymbol, bool& rbInvalidExternalNameRange );
diff --git a/sc/source/core/tool/address.cxx b/sc/source/core/tool/address.cxx
index c016102f2649..e0d4b0c78fe4 100644
--- a/sc/source/core/tool/address.cxx
+++ b/sc/source/core/tool/address.cxx
@@ -1056,6 +1056,38 @@ static ScRefFlags lcl_ScRange_Parse_XL_A1( ScRange& r,
     return nFlags;
 }
 
+// Compare ignore case ASCII.
+static bool lcl_isString( const sal_Unicode* p1, const OUString& rStr )
+{
+    const size_t n = rStr.getLength();
+    if (!n)
+        return false;
+    const sal_Unicode* p2 = rStr.getStr();
+    for (size_t i=0; i<n; ++i)
+    {
+        if (!p1[i])
+            return false;
+        if (p1[i] != p2[i])
+        {
+            sal_Unicode c1 = p1[i];
+            if ('A' <= c1 && c1 <= 'Z')
+                c1 += 0x20;
+            if (c1 < 'a' || 'z' < c1)
+                return false;   // not a letter
+
+            sal_Unicode c2 = p2[i];
+            if ('A' <= c2 && c2 <= 'Z')
+                c2 += 0x20;
+            if (c2 < 'a' || 'z' < c2)
+                return false;   // not a letter to match
+
+            if (c1 != c2)
+                return false;   // lower case doesn't match either
+        }
+    }
+    return true;
+}
+
 /**
     @param p        pointer to null-terminated sal_Unicode string
     @param rRawRes  returns ScRefFlags::... flags without the final check for full
@@ -1071,7 +1103,8 @@ static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDo
                                            ScRefFlags& rRawRes,
                                            ScAddress::ExternalInfo* pExtInfo,
                                            ScRange* pRange,
-                                           sal_Int32* pSheetEndPos )
+                                           sal_Int32* pSheetEndPos,
+                                           const OUString* pErrRef )
 {
     const sal_Unicode* const pStart = p;
     if (pSheetEndPos)
@@ -1121,54 +1154,64 @@ static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDo
             p++;
         }
 
-        if (*p == '\'')
+        if (pErrRef && lcl_isString( p, *pErrRef) && p[pErrRef->getLength()] == '.')
         {
-            // Tokens that start at ' can have anything in them until a final
-            // ' but '' marks an escaped '.  We've earlier guaranteed that a
-            // string containing '' will be surrounded by '.
-            p = parseQuotedName(p, aTab);
+            // #REF! particle of an invalidated reference plus sheet separator.
+            p += pErrRef->getLength() + 1;
+            nRes &= ~ScRefFlags::TAB_VALID;
+            nTab = -1;
         }
         else
         {
-            OUStringBuffer aTabAcc;
-            while (*p)
+            if (*p == '\'')
             {
-                if( *p == '.')
-                    break;
+                // Tokens that start at ' can have anything in them until a final
+                // ' but '' marks an escaped '.  We've earlier guaranteed that a
+                // string containing '' will be surrounded by '.
+                p = parseQuotedName(p, aTab);
+            }
+            else
+            {
+                OUStringBuffer aTabAcc;
+                while (*p)
+                {
+                    if( *p == '.')
+                        break;
+
+                    if( *p == '\'' )
+                    {
+                        p++; break;
+                    }
+                    aTabAcc.append(*p);
+                    p++;
+                }
+                aTab = aTabAcc.makeStringAndClear();
+            }
+            if( *p++ != '.' )
+                nBits = ScRefFlags::ZERO;
 
-                if( *p == '\'' )
+            if (!bExtDoc && (!pDoc || !pDoc->GetTable( aTab, nTab )))
+            {
+                // Specified table name is not found in this document.  Assume this is an external document.
+                aDocName = aTab;
+                sal_Int32 n = aDocName.lastIndexOf('.');
+                if (n > 0)
                 {
-                    p++; break;
+                    // Extension found.  Strip it.
+                    aTab = aTab.replaceAt(n, 1, "");
+                    bExtDoc = true;
                 }
-                aTabAcc.append(*p);
-                p++;
+                else
+                    // No extension found.  This is probably not an external document.
+                    nBits = ScRefFlags::ZERO;
             }
-            aTab = aTabAcc.makeStringAndClear();
         }
-        if( *p++ != '.' )
-            nBits = ScRefFlags::ZERO;
 
         if (pSheetEndPos && (nBits & ScRefFlags::TAB_VALID))
         {
             *pSheetEndPos = p - pStart;
             nBailOutFlags = ScRefFlags::TAB_VALID | ScRefFlags::TAB_3D;
         }
-
-        if (!bExtDoc && (!pDoc || !pDoc->GetTable( aTab, nTab )))
-        {
-            // Specified table name is not found in this document.  Assume this is an external document.
-            aDocName = aTab;
-            sal_Int32 n = aDocName.lastIndexOf('.');
-            if (n > 0)
-            {
-                // Extension found.  Strip it.
-                aTab = aTab.replaceAt(n, 1, "");
-                bExtDoc = true;
-            }
-            else
-                // No extension found.  This is probably not an external document.
-                nBits = ScRefFlags::ZERO;
-        }
     }
     else
     {
@@ -1188,20 +1231,31 @@ static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDo
             p++;
         }
 
-        if (rtl::isAsciiAlpha( *p ))
+        if (pErrRef && lcl_isString( p, *pErrRef))
         {
-            nCol = sal::static_int_cast<SCCOL>( rtl::toAsciiUpperCase( *p++ ) - 'A' );
-            while (nCol < MAXCOL && rtl::isAsciiAlpha(*p))
-                nCol = sal::static_int_cast<SCCOL>( ((nCol + 1) * 26) + rtl::toAsciiUpperCase( *p++ ) - 'A' );
+            // #REF! particle of an invalidated reference.
+            p += pErrRef->getLength();
+            nBits &= ~ScRefFlags::COL_VALID;
+            nCol = -1;
         }
         else
-            nBits = ScRefFlags::ZERO;
+        {
+            if (rtl::isAsciiAlpha( *p ))
+            {
+                nCol = sal::static_int_cast<SCCOL>( rtl::toAsciiUpperCase( *p++ ) - 'A' );
+                while (nCol < MAXCOL && rtl::isAsciiAlpha(*p))
+                    nCol = sal::static_int_cast<SCCOL>( ((nCol + 1) * 26) + rtl::toAsciiUpperCase( *p++ ) - 'A' );
+            }
+            else
+                nBits = ScRefFlags::ZERO;
 
-        if (nCol > MAXCOL || (*p && *p != '$' && !rtl::isAsciiDigit( *p )))
-            nBits = ScRefFlags::ZERO;
+            if (nCol > MAXCOL || (*p && *p != '$' && !rtl::isAsciiDigit( *p ) &&
+                        (!pErrRef || !lcl_isString( p, *pErrRef))))
+                nBits = ScRefFlags::ZERO;
+            if( nBits == ScRefFlags::ZERO )
+                p = q;
+        }
         nRes |= nBits;
-        if( nBits == ScRefFlags::ZERO )
-            p = q;
     }
 
     q = p;
@@ -1213,23 +1267,40 @@ static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDo
             nBits |= ScRefFlags::ROW_ABS;
             p++;
         }
-        if( !rtl::isAsciiDigit( *p ) )
+
+        if (pErrRef && lcl_isString( p, *pErrRef))
         {
-            nBits = ScRefFlags::ZERO;
-            nRow = SCROW(-1);
+            // #REF! particle of an invalidated reference.
+            p += pErrRef->getLength();
+            // Clearing the ROW_VALID bit here is not possible because of the
+            // check at the end whether only a valid column was detected in
+            // which case all bits are cleared because it could be any other
+            // name. Instead, set to an absolute invalid row value. This will
+            // display a $#REF! instead of #REF! if the error value was
+            // relative, but live with it.
+            nBits |= ScRefFlags::ROW_ABS;
+            nRow = -1;
         }
         else
         {
-            long n = rtl_ustr_toInt32( p, 10 ) - 1;
-            while (rtl::isAsciiDigit( *p ))
-                p++;
-            if( n < 0 || n > MAXROW )
+            if( !rtl::isAsciiDigit( *p ) )
+            {
                 nBits = ScRefFlags::ZERO;
-            nRow = static_cast<SCROW>(n);
+                nRow = SCROW(-1);
+            }
+            else
+            {
+                long n = rtl_ustr_toInt32( p, 10 ) - 1;
+                while (rtl::isAsciiDigit( *p ))
+                    p++;
+                if( n < 0 || n > MAXROW )
+                    nBits = ScRefFlags::ZERO;
+                nRow = static_cast<SCROW>(n);
+            }
+            if( nBits == ScRefFlags::ZERO )
+                p = q;
         }
         nRes |= nBits;
-        if( nBits == ScRefFlags::ZERO )
-            p = q;
     }
 
     rAddr.Set( nCol, nRow, nTab );
@@ -1346,7 +1417,8 @@ static ScRefFlags lcl_ScAddress_Parse ( const sal_Unicode* p, ScDocument* pDoc,
                                         const ScAddress::Details& rDetails,
                                         ScAddress::ExternalInfo* pExtInfo = nullptr,
                                         const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks = nullptr,
-                                        sal_Int32* pSheetEndPos = nullptr )
+                                        sal_Int32* pSheetEndPos = nullptr,
+                                        const OUString* pErrRef = nullptr )
 {
     if( !*p )
         return ScRefFlags::ZERO;
@@ -1375,7 +1447,7 @@ static ScRefFlags lcl_ScAddress_Parse ( const sal_Unicode* p, ScDocument* pDoc,
         case formula::FormulaGrammar::CONV_OOO:
         {
             ScRefFlags nRawRes = ScRefFlags::ZERO;
-            return lcl_ScAddress_Parse_OOo( p, pDoc, rAddr, nRawRes, pExtInfo, nullptr, pSheetEndPos);
+            return lcl_ScAddress_Parse_OOo( p, pDoc, rAddr, nRawRes, pExtInfo, nullptr, pSheetEndPos, pErrRef);
         }
     }
 }
@@ -1432,9 +1504,10 @@ ScRefFlags ScAddress::Parse( const OUString& r, ScDocument* pDoc,
                              const Details& rDetails,
                              ExternalInfo* pExtInfo,
                              const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks,
-                             sal_Int32* pSheetEndPos )
+                             sal_Int32* pSheetEndPos,
+                             const OUString* pErrRef )
 {
-    return lcl_ScAddress_Parse( r.getStr(), pDoc, *this, rDetails, pExtInfo, pExternalLinks, pSheetEndPos );
+    return lcl_ScAddress_Parse( r.getStr(), pDoc, *this, rDetails, pExtInfo, pExternalLinks, pSheetEndPos, pErrRef);
 }
 
 bool ScRange::Intersects( const ScRange& rRange ) const
@@ -1502,7 +1575,8 @@ void ScRange::ExtendTo( const ScRange& rRange )
 static ScRefFlags lcl_ScRange_Parse_OOo( ScRange& rRange,
                                          const OUString& r,
                                          ScDocument* pDoc,
-                                         ScAddress::ExternalInfo* pExtInfo = nullptr )
+                                         ScAddress::ExternalInfo* pExtInfo,
+                                         const OUString* pErrRef )
 {
     ScRefFlags nRes1 = ScRefFlags::ZERO, nRes2 = ScRefFlags::ZERO;
     sal_Int32 nPos = ScGlobal::FindUnquoted( r, ':');
@@ -1512,14 +1586,15 @@ static ScRefFlags lcl_ScRange_Parse_OOo( ScRange& rRange,
         aTmp[nPos] = 0;
         const sal_Unicode* p = aTmp.getStr();
         ScRefFlags nRawRes1 = ScRefFlags::ZERO;
-        nRes1 = lcl_ScAddress_Parse_OOo( p, pDoc, rRange.aStart, nRawRes1, pExtInfo, nullptr, nullptr);
+        nRes1 = lcl_ScAddress_Parse_OOo( p, pDoc, rRange.aStart, nRawRes1, pExtInfo, nullptr, nullptr, pErrRef);
         if ((nRes1 != ScRefFlags::ZERO) ||
                 ((nRawRes1 & (ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID)) &&
                  (nRawRes1 & ScRefFlags::TAB_VALID)))
         {
             rRange.aEnd = rRange.aStart;  // sheet must be initialized identical to first sheet
             ScRefFlags nRawRes2 = ScRefFlags::ZERO;
-            nRes2 = lcl_ScAddress_Parse_OOo( p + nPos+ 1, pDoc, rRange.aEnd, nRawRes2, pExtInfo, &rRange, nullptr);
+            nRes2 = lcl_ScAddress_Parse_OOo( p + nPos+ 1, pDoc, rRange.aEnd, nRawRes2,
+                    pExtInfo, &rRange, nullptr, pErrRef);
             if (!((nRes1 & ScRefFlags::VALID) && (nRes2 & ScRefFlags::VALID)) &&
                     // If not fully valid addresses, check if both have a valid
                     // column or row, and both have valid (or omitted) sheet references.
@@ -1622,7 +1697,8 @@ static ScRefFlags lcl_ScRange_Parse_OOo( ScRange& rRange,
 ScRefFlags ScRange::Parse( const OUString& rString, ScDocument* pDoc,
                            const ScAddress::Details& rDetails,
                            ScAddress::ExternalInfo* pExtInfo,
-                           const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks )
+                           const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks,
+                           const OUString* pErrRef )
 {
     if (rString.isEmpty())
         return ScRefFlags::ZERO;
@@ -1644,7 +1720,7 @@ ScRefFlags ScRange::Parse( const OUString& rString, ScDocument* pDoc,
         default:
         case formula::FormulaGrammar::CONV_OOO:
         {
-            return lcl_ScRange_Parse_OOo( *this, rString, pDoc, pExtInfo );
+            return lcl_ScRange_Parse_OOo( *this, rString, pDoc, pExtInfo, pErrRef );
         }
     }
 }
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 7340de6f8a37..cc2ddfbc2dbd 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -644,7 +644,7 @@ static bool lcl_parseExternalName(
     if (aTmpName[nNameLen-1] == '!')
     {
         // Check against #REF!.
-        if (aTmpName == "#REF!")
+        if (aTmpName.equalsIgnoreAsciiCase("#REF!"))
             return false;
     }
 
@@ -1920,6 +1920,28 @@ static sal_Unicode* lcl_UnicodeStrNCpy( sal_Unicode* pDst, const sal_Unicode* pS
     return pDst;
 }
 
+// p1 MUST contain at least n characters, or terminate with NIL.
+// p2 MUST pass upper case letters, if any.
+// n  MUST not be greater than length of p2
+static bool lcl_isUnicodeIgnoreAscii( const sal_Unicode* p1, const char* p2, size_t n )
+{
+    for (size_t i=0; i<n; ++i)
+    {
+        if (!p1[i])
+            return false;
+        if (p1[i] != p2[i])
+        {
+            if (p1[i] < 'a' || 'z' < p1[i])
+                return false;   // not a lower case letter
+            if (p2[i] < 'A' || 'Z' < p2[i])
+                return false;   // not a letter to match
+            if (p1[i] != p2[i] + 0x20)
+                return false;   // lower case doesn't match either
+        }
+    }
+    return true;
+}
+
 // NextSymbol
 
 // Parses the formula into separate symbols for further processing.
@@ -2142,6 +2164,33 @@ Label_MaskStateMachine:
                     else
                         *pSym++ = c;
                 }
+                else if (c == '#' && lcl_isUnicodeIgnoreAscii( pSrc, "REF!", 4))
+                {
+                    // Completely ugly means to catch broken
+                    // [$]#REF!.[$]#REF![$]#REF! (one or multiple parts)
+                    // references that were written in ODF named ranges
+                    // (without embracing [] hence no predetected reference)
+                    // and to OOXML and handle them as one symbol.
+                    // Also catches these in UI, so we can process them
+                    // further.
+                    int i = 0;
+                    for ( ; i<5; ++i)
+                    {
+                        if( pSym == &cSymbol[ MAXSTRLEN-1 ] )
+                        {
+                            SetError(errStringOverflow);
+                            eState = ssStop;
+                            break;  // for
+                        }
+                        else
+                        {
+                            *pSym++ = c;
+                            c = *pSrc++;
+                        }
+                    }
+                    if (i == 5)
+                        c = *((--pSrc)-1);  // position last/next character correctly
+                }
                 else if (c == ':' && mnRangeOpPosInSymbol < 0)
                 {
                     // One range operator may form Sheet1.A:A, which we need to
@@ -2282,8 +2331,18 @@ Label_MaskStateMachine:
                      * as opcode symbols will be recognized and others result
                      * in ocBad, so the result is actually conformant. */
                     bool bAdd = true;
-                    if ('!' == c || '?' == c)
+                    if ('?' == c)
                         eState = ssStop;
+                    else if ('!' == c)
+                    {
+                        // Check if this is #REF! that starts an invalid reference.
+                        // Note we have an implicit '!' here at the end.
+                        if (pSym - &cSymbol[0] == 4 && lcl_isUnicodeIgnoreAscii( cSymbol, "#REF", 4) &&
+                                ((GetCharTableFlags( *pSrc, c) & SC_COMPILER_C_IDENT) != 0))
+                            eState = ssGetIdent;
+                        else
+                            eState = ssStop;
+                    }
                     else if ('/' == c)
                     {
                         if (!bErrorConstantHadSlash)
@@ -2811,10 +2870,27 @@ bool ScCompiler::IsString()
     return false;
 }
 
-bool ScCompiler::IsPredetectedReference(const OUString& rName)
+bool ScCompiler::IsPredetectedErrRefReference( const OUString& rName, const OUString* pErrRef )
+{
+    switch (mnPredetectedReference)
+    {
+        case 1:
+            return IsSingleReference( rName, pErrRef);
+        case 2:
+            return IsDoubleReference( rName, pErrRef);
+        default:
+            return false;
+    }
+}
+
+bool ScCompiler::IsPredetectedReference( const OUString& rName )
 {
     // Speedup documents with lots of broken references, e.g. sheet deleted.
-    sal_Int32 nPos = rName.indexOf("#REF!");
+    // It could also be a broken invalidated reference that contains #REF!
+    // (but is not equal to), which we wrote prior to ODFF and also to ODFF
+    // between 2013 and 2016 until 5.1.4
+    const OUString aErrRef("#REF!");    // not localized in ODFF
+    sal_Int32 nPos = rName.indexOf( aErrRef);
     if (nPos != -1)
     {
         /* TODO: this may be enhanced by reusing scan information from
@@ -2830,13 +2906,17 @@ bool ScCompiler::IsPredetectedReference(const OUString& rName)
             // so pass it on.
             if (rName.getLength() == 5)
                 return IsErrorConstant( rName);
-            return false;           // #REF!.AB42 or #REF!42 or #REF!#REF!
+            // #REF!.AB42 or #REF!42 or #REF!#REF!
+            return IsPredetectedErrRefReference( rName, &aErrRef);
         }
         sal_Unicode c = rName[nPos-1];      // before #REF!
         if ('$' == c)
         {
             if (nPos == 1)
-                return false;       // $#REF!.AB42 or $#REF!42 or $#REF!#REF!
+            {
+                // $#REF!.AB42 or $#REF!42 or $#REF!#REF!
+                return IsPredetectedErrRefReference( rName, &aErrRef);
+            }
             c = rName[nPos-2];              // before $#REF!
         }
         sal_Unicode c2 = nPos+5 < rName.getLength() ? rName[nPos+5] : 0;     // after #REF!
@@ -2844,18 +2924,27 @@ bool ScCompiler::IsPredetectedReference(const OUString& rName)
         {
             case '.':
                 if ('$' == c2 || '#' == c2 || ('0' <= c2 && c2 <= '9'))
-                    return false;   // sheet.#REF!42 or sheet.#REF!#REF!
+                {
+                    // sheet.#REF!42 or sheet.#REF!#REF!
+                    return IsPredetectedErrRefReference( rName, &aErrRef);
+                }
                 break;
             case ':':
                 if (mnPredetectedReference > 1 &&
                         ('.' == c2 || '$' == c2 || '#' == c2 ||
                          ('0' <= c2 && c2 <= '9')))
-                    return false;   // :#REF!.AB42 or :#REF!42 or :#REF!#REF!
+                {
+                    // :#REF!.AB42 or :#REF!42 or :#REF!#REF!
+                    return IsPredetectedErrRefReference( rName, &aErrRef);
+                }
                 break;
             default:
                 if (rtl::isAsciiAlpha(c) &&
                         ((mnPredetectedReference > 1 && ':' == c2) || 0 == c2))
-                    return false;   // AB#REF!: or AB#REF!
+                {
+                    // AB#REF!: or AB#REF!
+                    return IsPredetectedErrRefReference( rName, &aErrRef);
+                }
         }
     }
     switch (mnPredetectedReference)
@@ -2868,12 +2957,12 @@ bool ScCompiler::IsPredetectedReference(const OUString& rName)
     return false;
 }
 
-bool ScCompiler::IsDoubleReference( const OUString& rName )
+bool ScCompiler::IsDoubleReference( const OUString& rName, const OUString* pErrRef )
 {
     ScRange aRange( aPos, aPos );
     const ScAddress::Details aDetails( pConv->meConv, aPos );
     ScAddress::ExternalInfo aExtInfo;
-    ScRefFlags nFlags = aRange.Parse( rName, pDoc, aDetails, &aExtInfo, &maExternalLinks );
+    ScRefFlags nFlags = aRange.Parse( rName, pDoc, aDetails, &aExtInfo, &maExternalLinks, pErrRef );
     if( nFlags & ScRefFlags::VALID )
     {
         ScComplexRefData aRef;
@@ -2908,14 +2997,15 @@ bool ScCompiler::IsDoubleReference( const OUString& rName )
     return ( nFlags & ScRefFlags::VALID ) != ScRefFlags::ZERO;
 }
 
-bool ScCompiler::IsSingleReference( const OUString& rName )
+bool ScCompiler::IsSingleReference( const OUString& rName, const OUString* pErrRef )
 {
     mnCurrentSheetEndPos = 0;
     mnCurrentSheetTab = -1;
     ScAddress aAddr( aPos );
     const ScAddress::Details aDetails( pConv->meConv, aPos );
     ScAddress::ExternalInfo aExtInfo;
-    ScRefFlags nFlags = aAddr.Parse( rName, pDoc, aDetails, &aExtInfo, &maExternalLinks, &mnCurrentSheetEndPos);
+    ScRefFlags nFlags = aAddr.Parse( rName, pDoc, aDetails,
+            &aExtInfo, &maExternalLinks, &mnCurrentSheetEndPos, pErrRef);
     // Something must be valid in order to recognize Sheet1.blah or blah.a1
     // as a (wrong) reference.
     if( nFlags & ( ScRefFlags::COL_VALID|ScRefFlags::ROW_VALID|ScRefFlags::TAB_VALID ) )
@@ -2972,7 +3062,7 @@ bool ScCompiler::IsSingleReference( const OUString& rName )
     return ( nFlags & ScRefFlags::VALID ) != ScRefFlags::ZERO;
 }
 
-bool ScCompiler::IsReference( const OUString& rName )
+bool ScCompiler::IsReference( const OUString& rName, const OUString* pErrRef )
 {
     // Has to be called before IsValue
     sal_Unicode ch1 = rName[0];
@@ -3023,7 +3113,7 @@ bool ScCompiler::IsReference( const OUString& rName )
         } while(false);
     }
 
-    if (IsSingleReference( rName))
+    if (IsSingleReference( rName, pErrRef))
         return true;
 
     // Though the range operator is handled explicitly, when encountering
@@ -3031,7 +3121,7 @@ bool ScCompiler::IsReference( const OUString& rName )
     // doesn't pass as single cell reference.
     if (mnRangeOpPosInSymbol > 0)   // ":foo" would be nonsense
     {
-        if (IsDoubleReference( rName))
+        if (IsDoubleReference( rName, pErrRef))
             return true;
         // Now try with a symbol up to the range operator, rewind source
         // position.
@@ -3058,7 +3148,7 @@ bool ScCompiler::IsReference( const OUString& rName )
                 SAL_FALLTHROUGH;
             case FormulaGrammar::CONV_XL_R1C1:
                 // C2 or C[1] are valid entire column references.
-                if (IsDoubleReference( rName))
+                if (IsDoubleReference( rName, pErrRef))
                     return true;
                 break;
             default:
@@ -3887,12 +3977,6 @@ bool ScCompiler::NextNewToken( bool bInArray )
         bool bInvalidExternalNameRange;
         if (!IsPredetectedReference( aStr) && !IsExternalNamedRange( aStr, bInvalidExternalNameRange ))
         {
-            /* TODO: it would be nice to generate a #REF! error here, which
-             * would need an ocBad token with additional error value.
-             * FormulaErrorToken wouldn't do because we want to preserve the
-             * original string containing partial valid address
-             * information if not ODFF (in that case it was already handled).
-             * */
             svl::SharedString aSS = pDoc->GetSharedStringPool().intern(aStr);
             maRawToken.SetString(aSS.getData(), aSS.getDataIgnoreCase());
             maRawToken.NewOpCode( ocBad );
@@ -3986,10 +4070,13 @@ bool ScCompiler::NextNewToken( bool bInArray )
                         return true;
                 }
 
-                // This can be only an error constant, if any.
+                // This can be either an error constant ...
                 if (IsErrorConstant( aUpper))
                     return true;
 
+                // ... or some invalidated reference starting with #REF!
+                // which is handled after the do loop.
+
                 break;  // do; create ocBad token or set error.
             }
             if (IsOpCode( aUpper, bInArray ))
@@ -4066,6 +4153,13 @@ bool ScCompiler::NextNewToken( bool bInArray )
 
     } while (mbRewind);
 
+    // Last chance: it could be a broken invalidated reference that contains
+    // #REF! (but is not equal to), which we also wrote to ODFF between 2013
+    // and 2016 until 5.1.4
+    OUString aErrRef( mxSymbols->getSymbol( ocErrRef));
+    if (aUpper.indexOf( aErrRef) >= 0 && IsReference( aUpper, &aErrRef))
+        return true;
+
     if ( meExtendedErrorDetection != EXTENDED_ERROR_DETECTION_NONE )
     {
         // set an error