Resolves: tdf#96915 implement other-sheet-local named expressions

Change-Id: I0d62536caa6eb455473a755067abc585662cd9a5

20 files changed, 321 insertions, 125 deletions

diff --git a/formula/source/core/api/token.cxx b/formula/source/core/api/token.cxx
index 22d3c84bb9db..7009d94e4756 100644
--- a/formula/source/core/api/token.cxx
+++ b/formula/source/core/api/token.cxx
@@ -221,15 +221,15 @@ void FormulaToken::SetIndex( sal_uInt16 )
     SAL_WARN( "formula.core", "FormulaToken::SetIndex: virtual dummy called" );
 }
 
-bool FormulaToken::IsGlobal() const
+sal_Int16 FormulaToken::GetSheet() const
 {
-    SAL_WARN( "formula.core", "FormulaToken::IsGlobal: virtual dummy called" );
-    return true;
+    SAL_WARN( "formula.core", "FormulaToken::GetSheet: virtual dummy called" );
+    return -1;
 }
 
-void FormulaToken::SetGlobal( bool )
+void FormulaToken::SetSheet( sal_Int16 )
 {
-    SAL_WARN( "formula.core", "FormulaToken::SetGlobal: virtual dummy called" );
+    SAL_WARN( "formula.core", "FormulaToken::SetSheet: virtual dummy called" );
 }
 
 short* FormulaToken::GetJump() const
@@ -1724,12 +1724,12 @@ bool FormulaStringOpToken::operator==( const FormulaToken& r ) const
 
 sal_uInt16  FormulaIndexToken::GetIndex() const             { return nIndex; }
 void        FormulaIndexToken::SetIndex( sal_uInt16 n )     { nIndex = n; }
-bool        FormulaIndexToken::IsGlobal() const             { return mbGlobal; }
-void        FormulaIndexToken::SetGlobal( bool b )          { mbGlobal = b; }
+sal_Int16   FormulaIndexToken::GetSheet() const             { return mnSheet; }
+void        FormulaIndexToken::SetSheet( sal_Int16 n )      { mnSheet = n; }
 bool FormulaIndexToken::operator==( const FormulaToken& r ) const
 {
     return FormulaToken::operator==( r ) && nIndex == r.GetIndex() &&
-        mbGlobal == r.IsGlobal();
+        mnSheet == r.GetSheet();
 }
 const OUString& FormulaExternalToken::GetExternal() const       { return aExternal; }
 sal_uInt8       FormulaExternalToken::GetByte() const           { return nByte; }
diff --git a/include/formula/token.hxx b/include/formula/token.hxx
index 618168ecb34b..e0cd6b77ab87 100644
--- a/include/formula/token.hxx
+++ b/include/formula/token.hxx
@@ -154,8 +154,8 @@ public:
     virtual void                SetString( const svl::SharedString& rStr );
     virtual sal_uInt16          GetIndex() const;
     virtual void                SetIndex( sal_uInt16 n );
-    virtual bool                IsGlobal() const;
-    virtual void                SetGlobal( bool b );
+    virtual sal_Int16           GetSheet() const;
+    virtual void                SetSheet( sal_Int16 n );
     virtual short*              GetJump() const;
     virtual const OUString&     GetExternal() const;
     virtual FormulaToken*       GetFAPOrigToken() const;
@@ -310,18 +310,18 @@ class FORMULA_DLLPUBLIC FormulaIndexToken : public FormulaToken
 {
 private:
             sal_uInt16          nIndex;
-            bool                mbGlobal;
+            sal_Int16           mnSheet;
 public:
-                                FormulaIndexToken( OpCode e, sal_uInt16 n, bool bGlobal = true ) :
-                                    FormulaToken(  svIndex, e ), nIndex( n ), mbGlobal( bGlobal ) {}
+                                FormulaIndexToken( OpCode e, sal_uInt16 n, sal_Int16 nSheet = -1 ) :
+                                    FormulaToken(  svIndex, e ), nIndex( n ), mnSheet( nSheet ) {}
                                 FormulaIndexToken( const FormulaIndexToken& r ) :
-                                    FormulaToken( r ), nIndex( r.nIndex ), mbGlobal( r.mbGlobal ) {}
+                                    FormulaToken( r ), nIndex( r.nIndex ), mnSheet( r.mnSheet ) {}
 
     virtual FormulaToken*       Clone() const override { return new FormulaIndexToken(*this); }
     virtual sal_uInt16          GetIndex() const override;
     virtual void                SetIndex( sal_uInt16 n ) override;
-    virtual bool                IsGlobal() const override;
-    virtual void                SetGlobal( bool b ) override;
+    virtual sal_Int16           GetSheet() const override;
+    virtual void                SetSheet( sal_Int16 n ) override;
     virtual bool                operator==( const FormulaToken& rToken ) const override;
 };
 
diff --git a/sc/inc/address.hxx b/sc/inc/address.hxx
index a917de79d0c8..218f3462132c 100644
--- a/sc/inc/address.hxx
+++ b/sc/inc/address.hxx
@@ -308,11 +308,19 @@ public:
         nTabP = nTab;
     }
 
+    /**
+        @param  pSheetEndPos
+                If given and Parse() sucessfully parsed a sheet name it returns
+                the end position (exclusive) behind the sheet name AND a
+                following sheet name separator. This independent of whether the
+                resulting reference is fully valid or not.
+     */
     SC_DLLPUBLIC ScRefFlags Parse(
                     const OUString&, ScDocument* = nullptr,
                     const Details& rDetails = detailsOOOa1,
                     ExternalInfo* pExtInfo = nullptr,
-                    const css::uno::Sequence<css::sheet::ExternalLinkInfo>* pExternalLinks = nullptr );
+                    const css::uno::Sequence<css::sheet::ExternalLinkInfo>* pExternalLinks = nullptr,
+                    sal_Int32* pSheetEndPos = nullptr );
 
     SC_DLLPUBLIC void Format( OStringBuffer& r, ScRefFlags nFlags = ScRefFlags::ZERO,
                                   const ScDocument* pDocument = nullptr,
diff --git a/sc/inc/compiler.hxx b/sc/inc/compiler.hxx
index 2af43e4561b8..e1887aa10d27 100644
--- a/sc/inc/compiler.hxx
+++ b/sc/inc/compiler.hxx
@@ -120,7 +120,7 @@ public:
             sal_Unicode cName[MAXSTRLEN+1];
         } extname;
         struct {
-            bool        bGlobal;
+            sal_Int16   nSheet;
             sal_uInt16  nIndex;
         } name;
         struct {
@@ -157,7 +157,7 @@ public:
     void SetErrorConstant( sal_uInt16 nErr );
 
     // These methods are ok to use, reference count not cleared.
-    void SetName(bool bGlobal, sal_uInt16 nIndex);
+    void SetName(sal_Int16 nSheet, sal_uInt16 nIndex);
     void SetExternalSingleRef( sal_uInt16 nFileId, const OUString& rTabName, const ScSingleRefData& rRef );
     void SetExternalDoubleRef( sal_uInt16 nFileId, const OUString& rTabName, const ScComplexRefData& rRef );
     void SetExternalName( sal_uInt16 nFileId, const OUString& rName );
@@ -272,6 +272,9 @@ private:
 
     SvNumberFormatter* mpFormatter;
 
+    SCTAB       mnCurrentSheetTab;      // indicates current sheet number parsed so far
+    sal_Int32   mnCurrentSheetEndPos;   // position after current sheet name if parsed
+
     // For CONV_XL_OOX, may be set via API by MOOXML filter.
     css::uno::Sequence<css::sheet::ExternalLinkInfo> maExternalLinks;
 
diff --git a/sc/inc/document.hxx b/sc/inc/document.hxx
index 7da2b4d50849..825e511a0c07 100644
--- a/sc/inc/document.hxx
+++ b/sc/inc/document.hxx
@@ -535,6 +535,15 @@ public:
     void SetRangeName(SCTAB nTab, ScRangeName* pNew);
     void SetRangeName( ScRangeName* pNewRangeName );
 
+    /** Find a named expression / range name in either global or a local scope.
+        @param  nIndex
+                Index of named expression / range name.
+        @param  nTab
+                If <0 search nIndex in global scope, if >=0 search nIndex in scope of nTab.
+        @return nullptr if indexed name not found.
+     */
+    ScRangeData* FindRangeNameByIndexAndSheet( sal_uInt16 nIndex, SCTAB nTab ) const;
+
     /**
      * Call this immediately before updating all named ranges.
      */
diff --git a/sc/inc/token.hxx b/sc/inc/token.hxx
index 63c5ec77398b..b056d417afdd 100644
--- a/sc/inc/token.hxx
+++ b/sc/inc/token.hxx
@@ -231,7 +231,7 @@ public:
 
     virtual sal_uInt16          GetIndex() const override;
     virtual void                SetIndex( sal_uInt16 n ) override;
-    virtual bool                IsGlobal() const override;
+    virtual sal_Int16           GetSheet() const override;
     virtual bool                operator==( const formula::FormulaToken& rToken ) const override;
     virtual FormulaToken*       Clone() const override { return new ScTableRefToken(*this); }
 
diff --git a/sc/inc/tokenarray.hxx b/sc/inc/tokenarray.hxx
index 26d6ca48b0bf..e4953466d81e 100644
--- a/sc/inc/tokenarray.hxx
+++ b/sc/inc/tokenarray.hxx
@@ -95,7 +95,7 @@ public:
     /** ScSingleRefOpToken with ocMatRef. */
     formula::FormulaToken* AddMatrixSingleReference( const ScSingleRefData& rRef );
     formula::FormulaToken* AddDoubleReference( const ScComplexRefData& rRef );
-    formula::FormulaToken* AddRangeName( sal_uInt16 n, bool bGlobal );
+    formula::FormulaToken* AddRangeName( sal_uInt16 n, sal_Int16 nSheet );
     formula::FormulaToken* AddDBRange( sal_uInt16 n );
     formula::FormulaToken* AddExternalName( sal_uInt16 nFileId, const OUString& rName );
     void AddExternalSingleReference( sal_uInt16 nFileId, const OUString& rTabName, const ScSingleRefData& rRef );
diff --git a/sc/source/core/data/conditio.cxx b/sc/source/core/data/conditio.cxx
index b67b04f9666c..84de73c54dd4 100644
--- a/sc/source/core/data/conditio.cxx
+++ b/sc/source/core/data/conditio.cxx
@@ -107,7 +107,7 @@ static bool lcl_HasRelRef( ScDocument* pDoc, ScTokenArray* pFormula, sal_uInt16
                 case svIndex:
                 {
                     if( t->GetOpCode() == ocName )      // DB areas always absolute
-                        if( ScRangeData* pRangeData = pDoc->GetRangeName()->findByIndex( t->GetIndex() ) )
+                        if( ScRangeData* pRangeData = pDoc->FindRangeNameByIndexAndSheet( t->GetIndex(), t->GetSheet()) )
                             if( (nRecursion < 42) && lcl_HasRelRef( pDoc, pRangeData->GetCode(), nRecursion + 1 ) )
                                 return true;
                 }
diff --git a/sc/source/core/data/documen3.cxx b/sc/source/core/data/documen3.cxx
index 9f10d88b6e36..bada3affed38 100644
--- a/sc/source/core/data/documen3.cxx
+++ b/sc/source/core/data/documen3.cxx
@@ -234,6 +234,12 @@ const ScRangeData* ScDocument::GetRangeAtBlock( const ScRange& rBlock, OUString*
     return pData;
 }
 
+ScRangeData* ScDocument::FindRangeNameByIndexAndSheet( sal_uInt16 nIndex, SCTAB nTab ) const
+{
+    const ScRangeName* pRN = (nTab < 0 ? GetRangeName() : GetRangeName(nTab));
+    return (pRN ? pRN->findByIndex( nIndex) : nullptr);
+}
+
 void ScDocument::SetDBCollection( ScDBCollection* pNewDBCollection, bool bRemoveAutoFilter )
 {
     if (pDBCollection && bRemoveAutoFilter)
diff --git a/sc/source/core/data/formulacell.cxx b/sc/source/core/data/formulacell.cxx
index c97e330f2fdd..0db0ab4f65a5 100644
--- a/sc/source/core/data/formulacell.cxx
+++ b/sc/source/core/data/formulacell.cxx
@@ -422,16 +422,22 @@ bool lcl_isReference(const FormulaToken& rToken)
 
 void adjustRangeName(formula::FormulaToken* pToken, ScDocument& rNewDoc, const ScDocument* pOldDoc, const ScAddress& aNewPos, const ScAddress& aOldPos)
 {
-    bool bOldGlobal = pToken->IsGlobal();
-    SCTAB aOldTab = aOldPos.Tab();
+    bool bSameDoc = (rNewDoc.GetPool() == const_cast<ScDocument*>(pOldDoc)->GetPool());
+    SCTAB nOldSheet = pToken->GetSheet();
+    if (bSameDoc && (nOldSheet < 0 || nOldSheet != aOldPos.Tab()))
+        // Same doc and global name or sheet-local name on other sheet stays
+        // the same.
+        return;
+
     OUString aRangeName;
     int nOldIndex = pToken->GetIndex();
     ScRangeData* pOldRangeData = nullptr;
 
     //search the name of the RangeName
-    if (!bOldGlobal)
+    if (nOldSheet >= 0)
     {
-        pOldRangeData = pOldDoc->GetRangeName(aOldTab)->findByIndex(nOldIndex);
+        const ScRangeName* pRangeName = pOldDoc->GetRangeName(nOldSheet);
+        pOldRangeData = pRangeName ? pRangeName->findByIndex(nOldIndex) : nullptr;
         if (!pOldRangeData)
             return;     //might be an error in the formula array
         aRangeName = pOldRangeData->GetUpperName();
@@ -444,12 +450,15 @@ void adjustRangeName(formula::FormulaToken* pToken, ScDocument& rNewDoc, const S
         aRangeName = pOldRangeData->GetUpperName();
     }
 
+    SAL_WARN_IF( !bSameDoc && nOldSheet >= 0 && nOldSheet != aOldPos.Tab(),
+            "sc.core", "adjustRangeName - sheet-local name was on other sheet in other document");
+    /* TODO: can we do something about that? e.g. loop over sheets? */
+
     //find corresponding range name in new document
     //first search for local range name then global range names
-    SCTAB aNewTab = aNewPos.Tab();
-    ScRangeName* pRangeName = rNewDoc.GetRangeName(aNewTab);
+    SCTAB nNewSheet = aNewPos.Tab();
+    ScRangeName* pRangeName = rNewDoc.GetRangeName(nNewSheet);
     ScRangeData* pRangeData = nullptr;
-    bool bNewGlobal = false;
     //search local range names
     if (pRangeName)
     {
@@ -458,7 +467,7 @@ void adjustRangeName(formula::FormulaToken* pToken, ScDocument& rNewDoc, const S
     //search global range names
     if (!pRangeData)
     {
-        bNewGlobal = true;
+        nNewSheet = -1;
         pRangeName = rNewDoc.GetRangeName();
         if (pRangeName)
             pRangeData = pRangeName->findByUpperName(aRangeName);
@@ -466,21 +475,24 @@ void adjustRangeName(formula::FormulaToken* pToken, ScDocument& rNewDoc, const S
     //if no range name was found copy it
     if (!pRangeData)
     {
-        bNewGlobal = bOldGlobal;
+        if (nOldSheet < 0)
+            nNewSheet = -1;
+        else
+            nNewSheet = aNewPos.Tab();
         pRangeData = new ScRangeData(*pOldRangeData, &rNewDoc);
         pRangeData->SetIndex(0);    // needed for insert to assign a new index
         ScTokenArray* pRangeNameToken = pRangeData->GetCode();
-        if (rNewDoc.GetPool() != const_cast<ScDocument*>(pOldDoc)->GetPool())
+        if (!bSameDoc)
         {
             pRangeNameToken->ReadjustAbsolute3DReferences(pOldDoc, &rNewDoc, pRangeData->GetPos(), true);
             pRangeNameToken->AdjustAbsoluteRefs(pOldDoc, aOldPos, aNewPos, false, true);
         }
 
         bool bInserted;
-        if (bNewGlobal)
+        if (nNewSheet < 0)
             bInserted = rNewDoc.GetRangeName()->insert(pRangeData);
         else
-            bInserted = rNewDoc.GetRangeName(aNewTab)->insert(pRangeData);
+            bInserted = rNewDoc.GetRangeName(nNewSheet)->insert(pRangeData);
         if (!bInserted)
         {
             //if this happened we have a real problem
@@ -492,7 +504,7 @@ void adjustRangeName(formula::FormulaToken* pToken, ScDocument& rNewDoc, const S
     }
     sal_Int32 nIndex = pRangeData->GetIndex();
     pToken->SetIndex(nIndex);
-    pToken->SetGlobal(bNewGlobal);
+    pToken->SetSheet(nNewSheet);
 }
 
 void adjustDBRange(formula::FormulaToken* pToken, ScDocument& rNewDoc, const ScDocument* pOldDoc)
@@ -3603,7 +3615,7 @@ void ScFormulaCell::UpdateTranspose( const ScRange& rSource, const ScAddress& rD
     {
         if( t->GetOpCode() == ocName )
         {
-            ScRangeData* pName = pDocument->GetRangeName()->findByIndex( t->GetIndex() );
+            const ScRangeData* pName = pDocument->FindRangeNameByIndexAndSheet( t->GetIndex(), t->GetSheet());
             if (pName)
             {
                 if (pName->IsModified())
@@ -3657,7 +3669,7 @@ void ScFormulaCell::UpdateGrow( const ScRange& rArea, SCCOL nGrowX, SCROW nGrowY
     {
         if( t->GetOpCode() == ocName )
         {
-            ScRangeData* pName = pDocument->GetRangeName()->findByIndex( t->GetIndex() );
+            const ScRangeData* pName = pDocument->FindRangeNameByIndexAndSheet( t->GetIndex(), t->GetSheet());
             if (pName)
             {
                 if (pName->IsModified())
@@ -3694,6 +3706,12 @@ static void lcl_FindRangeNamesInUse(std::set<sal_uInt16>& rIndexes, ScTokenArray
     {
         if (p->GetOpCode() == ocName)
         {
+            /* TODO: this should collect also sheet-local names, currently it
+             * collects only global names. But looking even for indexes of
+             * local names.. this always was wrong. Probably use
+             * UpdatedRangeNames instead of the set, but doing so would also
+             * need more work in copying things over clipboard and such. */
+
             sal_uInt16 nTokenIndex = p->GetIndex();
             rIndexes.insert( nTokenIndex );
 
diff --git a/sc/source/core/data/validat.cxx b/sc/source/core/data/validat.cxx
index 2535990a8849..278ae4ececa1 100644
--- a/sc/source/core/data/validat.cxx
+++ b/sc/source/core/data/validat.cxx
@@ -694,7 +694,7 @@ bool ScValidationData::GetSelectionFromFormula(
         }
         else if (eOpCode == ocName)
         {
-            ScRangeData* pName = pDocument->GetRangeName()->findByIndex( t->GetIndex() );
+            const ScRangeData* pName = pDocument->FindRangeNameByIndexAndSheet( t->GetIndex(), t->GetSheet());
             if (pName && pName->IsReference(aRange))
             {
                 bRef = true;
diff --git a/sc/source/core/tool/address.cxx b/sc/source/core/tool/address.cxx
index ef2e492a55ee..c016102f2649 100644
--- a/sc/source/core/tool/address.cxx
+++ b/sc/source/core/tool/address.cxx
@@ -704,8 +704,12 @@ static ScRefFlags lcl_ScRange_Parse_XL_R1C1( ScRange& r,
                                              ScDocument* pDoc,
                                              const ScAddress::Details& rDetails,
                                              bool bOnlyAcceptSingle,
-                                             ScAddress::ExternalInfo* pExtInfo )
+                                             ScAddress::ExternalInfo* pExtInfo,
+                                             sal_Int32* pSheetEndPos )
 {
+    const sal_Unicode* const pStart = p;
+    if (pSheetEndPos)
+        *pSheetEndPos = 0;
     const sal_Unicode* pTmp = nullptr;
     OUString aExternDocName, aStartTabName, aEndTabName;
     ScRefFlags nFlags = ScRefFlags::VALID | ScRefFlags::TAB_VALID;
@@ -715,6 +719,13 @@ static ScRefFlags lcl_ScRange_Parse_XL_R1C1( ScRange& r,
     p = r.Parse_XL_Header( p, pDoc, aExternDocName, aStartTabName,
             aEndTabName, nFlags, bOnlyAcceptSingle );
 
+    ScRefFlags nBailOutFlags = ScRefFlags::ZERO;
+    if (pSheetEndPos && pStart < p && (nFlags & ScRefFlags::TAB_VALID) && (nFlags & ScRefFlags::TAB_3D))
+    {
+        *pSheetEndPos = p - pStart;
+        nBailOutFlags = ScRefFlags::TAB_VALID | ScRefFlags::TAB_3D;
+    }
+
     if (!aExternDocName.isEmpty())
         lcl_ScRange_External_TabSpan( r, nFlags, pExtInfo, aExternDocName,
                 aStartTabName, aEndTabName, pDoc);
@@ -725,7 +736,7 @@ static ScRefFlags lcl_ScRange_Parse_XL_R1C1( ScRange& r,
     if( *p == 'R' || *p == 'r' )
     {
         if( nullptr == (p = lcl_r1c1_get_row( p, rDetails, &r.aStart, &nFlags )) )
-            return ScRefFlags::ZERO;
+            return nBailOutFlags;
 
         if( *p != 'C' && *p != 'c' )    // full row R#
         {
@@ -800,7 +811,7 @@ static ScRefFlags lcl_ScRange_Parse_XL_R1C1( ScRange& r,
     else if( *p == 'C' || *p == 'c' )   // full col C#
     {
         if( nullptr == (p = lcl_r1c1_get_col( p, rDetails, &r.aStart, &nFlags )))
-            return ScRefFlags::ZERO;
+            return nBailOutFlags;
 
         if( p[0] != ':' || (p[1] != 'C' && p[1] != 'c') ||
             nullptr == (pTmp = lcl_r1c1_get_col( p+1, rDetails, &r.aEnd, &nFlags2 )))
@@ -831,7 +842,7 @@ static ScRefFlags lcl_ScRange_Parse_XL_R1C1( ScRange& r,
         return bOnlyAcceptSingle ? ScRefFlags::ZERO : nFlags;
     }
 
-    return ScRefFlags::ZERO;
+    return nBailOutFlags;
 }
 
 static inline const sal_Unicode* lcl_a1_get_col( const sal_Unicode* p,
@@ -897,8 +908,12 @@ static ScRefFlags lcl_ScRange_Parse_XL_A1( ScRange& r,
                                            ScDocument* pDoc,
                                            bool bOnlyAcceptSingle,
                                            ScAddress::ExternalInfo* pExtInfo,
-                                           const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks )
+                                           const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks,
+                                           sal_Int32* pSheetEndPos )
 {
+    const sal_Unicode* const pStart = p;
+    if (pSheetEndPos)
+        *pSheetEndPos = 0;
     const sal_Unicode* tmp1, *tmp2;
     OUString aExternDocName, aStartTabName, aEndTabName; // for external link table
     ScRefFlags nFlags = ScRefFlags::VALID | ScRefFlags::TAB_VALID, nFlags2 = ScRefFlags::TAB_VALID;
@@ -906,29 +921,36 @@ static ScRefFlags lcl_ScRange_Parse_XL_A1( ScRange& r,
     p = r.Parse_XL_Header( p, pDoc, aExternDocName, aStartTabName,
             aEndTabName, nFlags, bOnlyAcceptSingle, pExternalLinks );
 
+    ScRefFlags nBailOutFlags = ScRefFlags::ZERO;
+    if (pSheetEndPos && pStart < p && (nFlags & ScRefFlags::TAB_VALID) && (nFlags & ScRefFlags::TAB_3D))
+    {
+        *pSheetEndPos = p - pStart;
+        nBailOutFlags = ScRefFlags::TAB_VALID | ScRefFlags::TAB_3D;
+    }
+
     if (!aExternDocName.isEmpty())
         lcl_ScRange_External_TabSpan( r, nFlags, pExtInfo, aExternDocName,
                 aStartTabName, aEndTabName, pDoc);
 
     if( nullptr == p )
-        return ScRefFlags::ZERO;
+        return nBailOutFlags;
 
     tmp1 = lcl_a1_get_col( p, &r.aStart, &nFlags );
     if( tmp1 == nullptr )          // Is it a row only reference 3:5
     {
         if( bOnlyAcceptSingle ) // by definition full row refs are ranges
-            return ScRefFlags::ZERO;
+            return nBailOutFlags;
 
         tmp1 = lcl_a1_get_row( p, &r.aStart, &nFlags );
 
         tmp1 = lcl_eatWhiteSpace( tmp1 );
         if( !tmp1 || *tmp1++ != ':' ) // Even a singleton requires ':' (eg 2:2)
-            return ScRefFlags::ZERO;
+            return nBailOutFlags;
 
         tmp1 = lcl_eatWhiteSpace( tmp1 );
         tmp2 = lcl_a1_get_row( tmp1, &r.aEnd, &nFlags2 );
         if( !tmp2 || *tmp2 != 0 )   // Must have fully parsed a singleton.
-            return ScRefFlags::ZERO;
+            return nBailOutFlags;
 
         r.aStart.SetCol( 0 ); r.aEnd.SetCol( MAXCOL );
         nFlags |=
@@ -942,16 +964,16 @@ static ScRefFlags lcl_ScRange_Parse_XL_A1( ScRange& r,
     if( tmp2 == nullptr )          // check for col only reference F:H
     {
         if( bOnlyAcceptSingle ) // by definition full col refs are ranges
-            return ScRefFlags::ZERO;
+            return nBailOutFlags;
 
         tmp1 = lcl_eatWhiteSpace( tmp1 );
         if( *tmp1++ != ':' )    // Even a singleton requires ':' (eg F:F)
-            return ScRefFlags::ZERO;
+            return nBailOutFlags;
 
         tmp1 = lcl_eatWhiteSpace( tmp1 );
         tmp2 = lcl_a1_get_col( tmp1, &r.aEnd, &nFlags2 );
         if( !tmp2 || *tmp2 != 0 )   // Must have fully parsed a singleton.
-            return ScRefFlags::ZERO;
+            return nBailOutFlags;
 
         r.aStart.SetRow( 0 ); r.aEnd.SetRow( MAXROW );
         nFlags |=
@@ -1047,8 +1069,13 @@ static ScRefFlags lcl_ScRange_Parse_XL_A1( ScRange& r,
  */
 static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDoc, ScAddress& rAddr,
                                            ScRefFlags& rRawRes,
-                                           ScAddress::ExternalInfo* pExtInfo = nullptr, ScRange* pRange = nullptr )
+                                           ScAddress::ExternalInfo* pExtInfo,
+                                           ScRange* pRange,
+                                           sal_Int32* pSheetEndPos )
 {
+    const sal_Unicode* const pStart = p;
+    if (pSheetEndPos)
+        *pSheetEndPos = 0;
     ScRefFlags  nRes = ScRefFlags::ZERO;
     rRawRes = ScRefFlags::ZERO;
     OUString aDocName;       // the pure Document Name
@@ -1061,7 +1088,6 @@ static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDo
     // document name is always quoted and has a trailing #.
     if (*p == '\'')
     {
-        const sal_Unicode* pStart = p;
         OUString aTmp;
         p = parseQuotedName(p, aTmp);
         aDocName = aTmp;
@@ -1081,7 +1107,8 @@ static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDo
     SCCOL   nCol = 0;
     SCROW   nRow = 0;
     SCTAB   nTab = 0;
-    ScRefFlags  nBits = ScRefFlags::TAB_VALID;
+    ScRefFlags nBailOutFlags = ScRefFlags::ZERO;
+    ScRefFlags nBits = ScRefFlags::TAB_VALID;
     const sal_Unicode* q;
     if ( ScGlobal::FindUnquoted( p, '.') )
     {
@@ -1121,6 +1148,12 @@ static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDo
         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.
@@ -1305,14 +1338,15 @@ static ScRefFlags lcl_ScAddress_Parse_OOo( const sal_Unicode* p, ScDocument* pDo
             nRes |= ScRefFlags::VALID;
     }
     else
-        nRes = ScRefFlags::ZERO;
+        nRes = nBailOutFlags;
     return nRes;
 }
 
 static ScRefFlags lcl_ScAddress_Parse ( const sal_Unicode* p, ScDocument* pDoc, ScAddress& rAddr,
                                         const ScAddress::Details& rDetails,
                                         ScAddress::ExternalInfo* pExtInfo = nullptr,
-                                        const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks = nullptr )
+                                        const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks = nullptr,
+                                        sal_Int32* pSheetEndPos = nullptr )
 {
     if( !*p )
         return ScRefFlags::ZERO;
@@ -1324,15 +1358,16 @@ static ScRefFlags lcl_ScAddress_Parse ( const sal_Unicode* p, ScDocument* pDoc,
         {
             ScRange rRange = rAddr;
             ScRefFlags nFlags = lcl_ScRange_Parse_XL_A1(
-                                    rRange, p, pDoc, true, pExtInfo,
-                                    (rDetails.eConv == formula::FormulaGrammar::CONV_XL_OOX ? pExternalLinks : nullptr) );
+                    rRange, p, pDoc, true, pExtInfo,
+                    (rDetails.eConv == formula::FormulaGrammar::CONV_XL_OOX ? pExternalLinks : nullptr),
+                    pSheetEndPos);
             rAddr = rRange.aStart;
             return nFlags;
         }
         case formula::FormulaGrammar::CONV_XL_R1C1:
         {
             ScRange rRange = rAddr;
-            ScRefFlags nFlags = lcl_ScRange_Parse_XL_R1C1( rRange, p, pDoc, rDetails, true, pExtInfo );
+            ScRefFlags nFlags = lcl_ScRange_Parse_XL_R1C1( rRange, p, pDoc, rDetails, true, pExtInfo, pSheetEndPos);
             rAddr = rRange.aStart;
             return nFlags;
         }
@@ -1340,7 +1375,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 );
+            return lcl_ScAddress_Parse_OOo( p, pDoc, rAddr, nRawRes, pExtInfo, nullptr, pSheetEndPos);
         }
     }
 }
@@ -1396,9 +1431,10 @@ bool ConvertDoubleRef( ScDocument* pDoc, const OUString& rRefString, SCTAB nDefT
 ScRefFlags ScAddress::Parse( const OUString& r, ScDocument* pDoc,
                              const Details& rDetails,
                              ExternalInfo* pExtInfo,
-                             const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks )
+                             const uno::Sequence<sheet::ExternalLinkInfo>* pExternalLinks,
+                             sal_Int32* pSheetEndPos )
 {
-    return lcl_ScAddress_Parse( r.getStr(), pDoc, *this, rDetails, pExtInfo, pExternalLinks );
+    return lcl_ScAddress_Parse( r.getStr(), pDoc, *this, rDetails, pExtInfo, pExternalLinks, pSheetEndPos );
 }
 
 bool ScRange::Intersects( const ScRange& rRange ) const
@@ -1476,13 +1512,14 @@ static ScRefFlags lcl_ScRange_Parse_OOo( ScRange& rRange,
         aTmp[nPos] = 0;
         const sal_Unicode* p = aTmp.getStr();
         ScRefFlags nRawRes1 = ScRefFlags::ZERO;
-        if (((nRes1 = lcl_ScAddress_Parse_OOo( p, pDoc, rRange.aStart, nRawRes1, pExtInfo)) != ScRefFlags::ZERO) ||
+        nRes1 = lcl_ScAddress_Parse_OOo( p, pDoc, rRange.aStart, nRawRes1, pExtInfo, nullptr, nullptr);
+        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);
+            nRes2 = lcl_ScAddress_Parse_OOo( p + nPos+ 1, pDoc, rRange.aEnd, nRawRes2, pExtInfo, &rRange, nullptr);
             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.
@@ -1596,12 +1633,12 @@ ScRefFlags ScRange::Parse( const OUString& rString, ScDocument* pDoc,
         case formula::FormulaGrammar::CONV_XL_OOX:
         {
             return lcl_ScRange_Parse_XL_A1( *this, rString.getStr(), pDoc, false, pExtInfo,
-                    (rDetails.eConv == formula::FormulaGrammar::CONV_XL_OOX ? pExternalLinks : nullptr) );
+                    (rDetails.eConv == formula::FormulaGrammar::CONV_XL_OOX ? pExternalLinks : nullptr), nullptr );
         }
 
         case formula::FormulaGrammar::CONV_XL_R1C1:
         {
-            return lcl_ScRange_Parse_XL_R1C1( *this, rString.getStr(), pDoc, rDetails, false, pExtInfo );
+            return lcl_ScRange_Parse_XL_R1C1( *this, rString.getStr(), pDoc, rDetails, false, pExtInfo, nullptr );
         }
 
         default:
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 2f88dd930d55..c57f192bf38a 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -2892,14 +2892,34 @@ bool ScCompiler::IsDoubleReference( const OUString& rName )
 
 bool ScCompiler::IsSingleReference( const OUString& rName )
 {
+    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 );
+    ScRefFlags nFlags = aAddr.Parse( rName, pDoc, aDetails, &aExtInfo, &maExternalLinks, &mnCurrentSheetEndPos);
     // 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 ) )
     {
+        // Valid given tab and invalid col or row may indicate a sheet-local
+        // named expression, bail out early and don't create a reference token.
+        if (!(nFlags & ScRefFlags::VALID) && mnCurrentSheetEndPos > 0 &&
+                (nFlags & ScRefFlags::TAB_VALID) && (nFlags & ScRefFlags::TAB_3D))
+        {
+            if (aExtInfo.mbExternal)
+            {
+                // External names are handled separately.
+                mnCurrentSheetEndPos = 0;
+                mnCurrentSheetTab = -1;
+            }
+            else
+            {
+                mnCurrentSheetTab = aAddr.Tab();
+            }
+            return false;
+        }
+
         ScSingleRefData aRef;
         aRef.InitAddress( aAddr );
         aRef.SetColRel( (nFlags & ScRefFlags::COL_ABS) == ScRefFlags::ZERO );
@@ -3093,8 +3113,8 @@ bool ScCompiler::IsNamedRange( const OUString& rUpperName )
     // IsNamedRange is called only from NextNewToken, with an upper-case string
 
     // try local names first
-    bool bGlobal = false;
-    ScRangeName* pRangeName = pDoc->GetRangeName(aPos.Tab());
+    sal_Int16 nSheet = aPos.Tab();
+    ScRangeName* pRangeName = pDoc->GetRangeName(nSheet);
     const ScRangeData* pData = nullptr;
     if (pRangeName)
         pData = pRangeName->findByUpperName(rUpperName);
@@ -3104,16 +3124,32 @@ bool ScCompiler::IsNamedRange( const OUString& rUpperName )
         if (pRangeName)
             pData = pRangeName->findByUpperName(rUpperName);
         if (pData)
-            bGlobal = true;
+            nSheet = -1;
     }
 
     if (pData)
     {
-        maRawToken.SetName(bGlobal, pData->GetIndex());
+        maRawToken.SetName( nSheet, pData->GetIndex());
         return true;
     }
-    else
-        return false;
+
+    // Sheet-local name with sheet specified.
+    if (mnCurrentSheetEndPos > 0 && mnCurrentSheetTab >= 0)
+    {
+        OUString aName( rUpperName.copy( mnCurrentSheetEndPos));
+        pRangeName = pDoc->GetRangeName( mnCurrentSheetTab);
+        if (pRangeName)
+        {
+            pData = pRangeName->findByUpperName(aName);
+            if (pData)
+            {
+                maRawToken.SetName( mnCurrentSheetTab, pData->GetIndex());
+                return true;
+            }
+        }
+    }
+
+    return false;
 }
 
 bool ScCompiler::IsExternalNamedRange( const OUString& rSymbol, bool& rbInvalidExternalNameRange )
@@ -3161,7 +3197,7 @@ bool ScCompiler::IsDBRange( const OUString& rName )
     if (!p)
         return false;
 
-    maRawToken.SetName(true, p->GetIndex()); // DB range is always global.
+    maRawToken.SetName( -1, p->GetIndex()); // DB range is always global.
     maRawToken.eOp = ocDBArea;
     return true;
 }
@@ -4387,19 +4423,7 @@ ScTokenArray* ScCompiler::CompileString( const OUString& rFormula, const OUStrin
 
 ScRangeData* ScCompiler::GetRangeData( const FormulaToken& rToken ) const
 {
-    ScRangeData* pRangeData = nullptr;
-    bool bGlobal = rToken.IsGlobal();
-    if (bGlobal)
-        // global named range.
-        pRangeData = pDoc->GetRangeName()->findByIndex( rToken.GetIndex());
-    else
-    {
-        // sheet local named range.
-        const ScRangeName* pRN = pDoc->GetRangeName( aPos.Tab());
-        if (pRN)
-            pRangeData = pRN->findByIndex( rToken.GetIndex());
-    }
-    return pRangeData;
+    return pDoc->FindRangeNameByIndexAndSheet( rToken.GetIndex(), rToken.GetSheet());
 }
 
 bool ScCompiler::HandleRange()
@@ -4759,7 +4783,20 @@ void ScCompiler::CreateStringFromIndex( OUStringBuffer& rBuffer, const FormulaTo
         {
             const ScRangeData* pData = GetRangeData( *_pTokenP);
             if (pData)
+            {
+                SCTAB nTab = _pTokenP->GetSheet();
+                if (nTab >= 0 && nTab != aPos.Tab())
+                {
+                    // Sheet-local on other sheet.
+                    OUString aName;
+                    if (pDoc->GetName( nTab, aName))
+                        aBuffer.append( aName);
+                    else
+                        aBuffer.append( ScGlobal::GetRscString( STR_NO_NAME_REF));
+                    aBuffer.append( pConv->getSpecialSymbol( ScCompiler::Convention::SHEET_SEPARATOR));
+                }
                 aBuffer.append(pData->GetName());
+            }
         }
         break;
         case ocDBArea:
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index 6a3f2712013d..3d82694c3a9e 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -293,12 +293,12 @@ void ScRawToken::SetErrorConstant( sal_uInt16 nErr )
     nError = nErr;
 }
 
-void ScRawToken::SetName(bool bGlobal, sal_uInt16 nIndex)
+void ScRawToken::SetName(sal_Int16 nSheet, sal_uInt16 nIndex)
 {
     eOp = ocName;
     eType = svIndex;
 
-    name.bGlobal = bGlobal;
+    name.nSheet = nSheet;
     name.nIndex = nIndex;
 }
 
@@ -409,7 +409,7 @@ FormulaToken* ScRawToken::CreateToken() const
             if (eOp == ocTableRef)
                 return new ScTableRefToken( table.nIndex, table.eItem);
             else
-                return new FormulaIndexToken( eOp, name.nIndex, name.bGlobal);
+                return new FormulaIndexToken( eOp, name.nIndex, name.nSheet);
         case svExternalSingleRef:
             {
                 OUString aTabName(extref.cTabName);
@@ -933,13 +933,13 @@ void ScTableRefToken::SetIndex( sal_uInt16 n )
     mnIndex = n;
 }
 
-bool ScTableRefToken::IsGlobal() const
+sal_Int16 ScTableRefToken::GetSheet() const
 {
     // Code asking for this may have to be adapted as it might assume an
     // svIndex token would always be ocName or ocDBArea.
-    SAL_WARN("sc.core","ScTableRefToken::IsGlobal - maybe adapt caller to know about TableRef?");
+    SAL_WARN("sc.core","ScTableRefToken::GetSheet - maybe adapt caller to know about TableRef?");
     // Database range is always global.
-    return true;
+    return -1;
 }
 
 ScTableRefToken::Item ScTableRefToken::GetItem() const
@@ -1212,7 +1212,16 @@ bool ScTokenArray::AddFormulaToken(
                         sheet::NameToken aTokenData;
                         rToken.Data >>= aTokenData;
                         if ( eOpCode == ocName )
-                            AddRangeName(aTokenData.Index, aTokenData.Global);
+                        {
+                            /* TODO: new token type with sheet number */
+                            if (aTokenData.Global)
+                                AddRangeName(aTokenData.Index, -1);
+                            else
+                                bError = true;
+                            /* FIXME: resolve the non-global case to the
+                             * current position's sheet as it implicitly was
+                             * before, currently this is broken. */
+                        }
                         else if (eOpCode == ocDBArea)
                             AddDBRange(aTokenData.Index);
                         else if (eOpCode == ocTableRef)
@@ -2059,9 +2068,9 @@ FormulaToken* ScTokenArray::AddMatrix( const ScMatrixRef& p )
     return Add( new ScMatrixToken( p ) );
 }
 
-FormulaToken* ScTokenArray::AddRangeName( sal_uInt16 n, bool bGlobal )
+FormulaToken* ScTokenArray::AddRangeName( sal_uInt16 n, sal_Int16 nSheet )
 {
-    return Add( new FormulaIndexToken( ocName, n, bGlobal));
+    return Add( new FormulaIndexToken( ocName, n, nSheet));
 }
 
 FormulaToken* ScTokenArray::AddDBRange( sal_uInt16 n )
@@ -2826,7 +2835,7 @@ bool expandRangeByEdge( const sc::RefUpdateContext& rCxt, ScRange& rRefRange, co
 bool isNameModified( const sc::UpdatedRangeNames& rUpdatedNames, SCTAB nOldTab, const formula::FormulaToken& rToken )
 {
     SCTAB nTab = -1;
-    if (!rToken.IsGlobal())
+    if (rToken.GetSheet() >= 0)
         nTab = nOldTab;
 
     // Check if this named expression has been modified.
@@ -3003,8 +3012,17 @@ sc::RefUpdateResult ScTokenArray::AdjustReferenceOnShift( const sc::RefUpdateCon
                 switch ((*pp)->GetOpCode())
                 {
                     case ocName:
-                        if (isNameModified(rCxt.maUpdatedNames, rOldPos.Tab(), **pp))
-                            aRes.mbNameModified = true;
+                        {
+                            SCTAB nOldTab = (*pp)->GetSheet();
+                            if (isNameModified(rCxt.maUpdatedNames, nOldTab, **pp))
+                                aRes.mbNameModified = true;
+                            if (rCxt.mnTabDelta &&
+                                    rCxt.maRange.aStart.Tab() <= nOldTab && nOldTab <= rCxt.maRange.aEnd.Tab())
+                            {
+                                aRes.mbNameModified = true;
+                                (*pp)->SetSheet( nOldTab + rCxt.mnTabDelta);
+                            }
+                        }
                         break;
                     case ocDBArea:
                     case ocTableRef:
@@ -3099,8 +3117,17 @@ sc::RefUpdateResult ScTokenArray::AdjustReferenceOnMove(
                 switch ((*pp)->GetOpCode())
                 {
                     case ocName:
-                        if (isNameModified(rCxt.maUpdatedNames, rOldPos.Tab(), **pp))
-                            aRes.mbNameModified = true;
+                        {
+                            SCTAB nOldTab = (*pp)->GetSheet();
+                            if (isNameModified(rCxt.maUpdatedNames, nOldTab, **pp))
+                                aRes.mbNameModified = true;
+                            if (rCxt.mnTabDelta &&
+                                    rCxt.maRange.aStart.Tab() <= nOldTab && nOldTab <= rCxt.maRange.aEnd.Tab())
+                            {
+                                aRes.mbNameModified = true;
+                                (*pp)->SetSheet( nOldTab + rCxt.mnTabDelta);
+                            }
+                        }
                         break;
                     case ocDBArea:
                     case ocTableRef:
@@ -3171,8 +3198,17 @@ sc::RefUpdateResult ScTokenArray::MoveReference( const ScAddress& rPos, const sc
                 switch ((*p)->GetOpCode())
                 {
                     case ocName:
-                        if (isNameModified(rCxt.maUpdatedNames, aOldRange.aStart.Tab(), **p))
-                            aRes.mbNameModified = true;
+                        {
+                            SCTAB nOldTab = (*p)->GetSheet();
+                            if (isNameModified(rCxt.maUpdatedNames, nOldTab, **p))
+                                aRes.mbNameModified = true;
+                            if (rCxt.mnTabDelta &&
+                                    rCxt.maRange.aStart.Tab() <= nOldTab && nOldTab <= rCxt.maRange.aEnd.Tab())
+                            {
+                                aRes.mbNameModified = true;
+                                (*p)->SetSheet( nOldTab + rCxt.mnTabDelta);
+                            }
+                        }
                         break;
                     case ocDBArea:
                     case ocTableRef:
@@ -3868,8 +3904,20 @@ sc::RefUpdateResult ScTokenArray::AdjustReferenceOnDeletedTab( sc::RefUpdateDele
                 switch ((*pp)->GetOpCode())
                 {
                     case ocName:
-                        if (isNameModified(rCxt.maUpdatedNames, rOldPos.Tab(), **pp))
-                            aRes.mbNameModified = true;
+                        {
+                            SCTAB nOldTab = (*pp)->GetSheet();
+                            if (isNameModified(rCxt.maUpdatedNames, nOldTab, **pp))
+                                aRes.mbNameModified = true;
+                            if (rCxt.mnDeletePos <= nOldTab)
+                            {
+                                aRes.mbNameModified = true;
+                                if (rCxt.mnDeletePos + rCxt.mnSheets < nOldTab)
+                                    (*pp)->SetSheet( nOldTab - rCxt.mnSheets);
+                                else
+                                    // Would point to a deleted sheet. Invalidate.
+                                    (*pp)->SetSheet( SCTAB_MAX);
+                            }
+                        }
                         break;
                     case ocDBArea:
                     case ocTableRef:
@@ -3932,8 +3980,16 @@ sc::RefUpdateResult ScTokenArray::AdjustReferenceOnInsertedTab( sc::RefUpdateIns
                 switch ((*pp)->GetOpCode())
                 {
                     case ocName:
-                        if (isNameModified(rCxt.maUpdatedNames, rOldPos.Tab(), **pp))
-                            aRes.mbNameModified = true;
+                        {
+                            SCTAB nOldTab = (*pp)->GetSheet();
+                            if (isNameModified(rCxt.maUpdatedNames, nOldTab, **pp))
+                                aRes.mbNameModified = true;
+                            if (rCxt.mnInsertPos <= nOldTab)
+                            {
+                                aRes.mbNameModified = true;
+                                (*pp)->SetSheet( nOldTab + rCxt.mnSheets);
+                            }
+                        }
                         break;
                     case ocDBArea:
                     case ocTableRef:
@@ -4017,8 +4073,17 @@ sc::RefUpdateResult ScTokenArray::AdjustReferenceOnMovedTab( sc::RefUpdateMoveTa
                 switch ((*pp)->GetOpCode())
                 {
                     case ocName:
-                        if (isNameModified(rCxt.maUpdatedNames, rOldPos.Tab(), **pp))
-                            aRes.mbNameModified = true;
+                        {
+                            SCTAB nOldTab = (*pp)->GetSheet();
+                            if (isNameModified(rCxt.maUpdatedNames, nOldTab, **pp))
+                                aRes.mbNameModified = true;
+                            SCTAB nNewTab = rCxt.getNewTab( nOldTab);
+                            if (nNewTab != nOldTab)
+                            {
+                                aRes.mbNameModified = true;
+                                (*pp)->SetSheet( nNewTab);
+                            }
+                        }
                         break;
                     case ocDBArea:
                     case ocTableRef:
@@ -4614,7 +4679,8 @@ void appendTokenByType( sc::TokenStringContext& rCxt, OUStringBuffer& rBuf, cons
             {
                 case ocName:
                 {
-                    if (rToken.IsGlobal())
+                    SCTAB nTab = rToken.GetSheet();
+                    if (nTab < 0)
                     {
                         // global named range
                         NameType::const_iterator it = rCxt.maGlobalRangeNames.find(nIndex);
@@ -4629,7 +4695,20 @@ void appendTokenByType( sc::TokenStringContext& rCxt, OUStringBuffer& rBuf, cons
                     else
                     {
                         // sheet-local named range
-                        sc::TokenStringContext::TabIndexMapType::const_iterator itTab = rCxt.maSheetRangeNames.find(rPos.Tab());
+                        if (nTab != rPos.Tab())
+                        {
+                            // On other sheet.
+                            OUString aName;
+                            if (static_cast<size_t>(nTab) < rCxt.maTabNames.size())
+                                aName = rCxt.maTabNames[nTab];
+                            if (!aName.isEmpty())
+                                rBuf.append( aName);
+                            else
+                                rBuf.append( ScGlobal::GetRscString( STR_NO_NAME_REF));
+                            rBuf.append( rCxt.mpRefConv->getSpecialSymbol( ScCompiler::Convention::SHEET_SEPARATOR));
+                        }
+
+                        sc::TokenStringContext::TabIndexMapType::const_iterator itTab = rCxt.maSheetRangeNames.find(nTab);
                         if (itTab == rCxt.maSheetRangeNames.end())
                         {
                             rBuf.append(ScGlobal::GetRscString(STR_NO_NAME_REF));
diff --git a/sc/source/filter/excel/excform.cxx b/sc/source/filter/excel/excform.cxx
index 2be528aefacd..33ec52fbfb5a 100644
--- a/sc/source/filter/excel/excform.cxx
+++ b/sc/source/filter/excel/excform.cxx
@@ -544,7 +544,7 @@ ConvErr ExcelToSc::Convert( const ScTokenArray*& pErgebnis, XclImpStream& aIn, s
                 if(pName && !pName->GetScRangeData())
                     aStack << aPool.Store( ocMacro, pName->GetXclName() );
                 else
-                    aStack << aPool.StoreName(nUINT16, true);
+                    aStack << aPool.StoreName(nUINT16, -1);
             }
                 break;
             case 0x44:
@@ -733,7 +733,7 @@ ConvErr ExcelToSc::Convert( const ScTokenArray*& pErgebnis, XclImpStream& aIn, s
                     aPool >> aStack;
                 }
                 else
-                    aStack << aPool.StoreName( nUINT16, true );
+                    aStack << aPool.StoreName( nUINT16, -1 );
                 aIn.Ignore( 12 );
                 break;
             }
diff --git a/sc/source/filter/excel/excform8.cxx b/sc/source/filter/excel/excform8.cxx
index c13876ca1dcc..f0668cb4f13f 100644
--- a/sc/source/filter/excel/excform8.cxx
+++ b/sc/source/filter/excel/excform8.cxx
@@ -493,7 +493,7 @@ ConvErr ExcelToSc8::Convert( const ScTokenArray*& rpTokArray, XclImpStream& aIn,
                         // user-defined macro name.
                         aStack << aPool.Store(ocMacro, pName->GetXclName());
                     else
-                        aStack << aPool.StoreName(nUINT16, pName->IsGlobal());
+                        aStack << aPool.StoreName(nUINT16, pName->IsGlobal() ? -1 : pName->GetScTab());
                 }
                 break;
             }
@@ -680,7 +680,7 @@ ConvErr ExcelToSc8::Convert( const ScTokenArray*& rpTokArray, XclImpStream& aIn,
                     if (pName)
                     {
                         if (pName->GetScRangeData())
-                            aStack << aPool.StoreName( nNameIdx, pName->IsGlobal());
+                            aStack << aPool.StoreName( nNameIdx, pName->IsGlobal() ? -1 : pName->GetScTab());
                         else
                             aStack << aPool.Store(ocMacro, pName->GetXclName());
                     }
diff --git a/sc/source/filter/excel/tokstack.cxx b/sc/source/filter/excel/tokstack.cxx
index 31e89959406c..9323f93572c2 100644
--- a/sc/source/filter/excel/tokstack.cxx
+++ b/sc/source/filter/excel/tokstack.cxx
@@ -425,7 +425,7 @@ bool TokenPool::GetElement( const sal_uInt16 nId )
                 if (n < maRangeNames.size())
                 {
                     const RangeName& r = maRangeNames[n];
-                    pScToken->AddRangeName(r.mnIndex, r.mbGlobal);
+                    pScToken->AddRangeName(r.mnIndex, r.mnSheet);
                 }
             }
             break;
@@ -623,7 +623,7 @@ const TokenId TokenPool::Store( const double& rDouble )
 
 const TokenId TokenPool::Store( const sal_uInt16 nIndex )
 {
-    return StoreName(nIndex, true);
+    return StoreName(nIndex, -1);
 }
 
 const TokenId TokenPool::Store( const OUString& rString )
@@ -790,7 +790,7 @@ const TokenId TokenPool::StoreMatrix()
     return static_cast<const TokenId>(nElementAkt);
 }
 
-const TokenId TokenPool::StoreName( sal_uInt16 nIndex, bool bGlobal )
+const TokenId TokenPool::StoreName( sal_uInt16 nIndex, sal_Int16 nSheet )
 {
     if ( nElementAkt >= nElement )
         if (!GrowElement())
@@ -802,7 +802,7 @@ const TokenId TokenPool::StoreName( sal_uInt16 nIndex, bool bGlobal )
     maRangeNames.push_back(RangeName());
     RangeName& r = maRangeNames.back();
     r.mnIndex = nIndex;
-    r.mbGlobal = bGlobal;
+    r.mnSheet = nSheet;
 
     ++nElementAkt;
 
diff --git a/sc/source/filter/excel/xeformula.cxx b/sc/source/filter/excel/xeformula.cxx
index 29ac7a0be289..9742cb6ad7ed 100644
--- a/sc/source/filter/excel/xeformula.cxx
+++ b/sc/source/filter/excel/xeformula.cxx
@@ -2079,10 +2079,8 @@ void XclExpFmlaCompImpl::ProcessExternalRangeRef( const XclExpScToken& rTokData
 
 void XclExpFmlaCompImpl::ProcessDefinedName( const XclExpScToken& rTokData )
 {
-    SCTAB nTab = SCTAB_GLOBAL;
-    bool bGlobal = rTokData.mpScToken->IsGlobal();
-    if (!bGlobal)
-        nTab = GetCurrScTab();
+    sal_Int16 nSheet = rTokData.mpScToken->GetSheet();
+    SCTAB nTab = (nSheet < 0 ? SCTAB_GLOBAL : nSheet);
 
     XclExpNameManager& rNameMgr = GetNameManager();
     sal_uInt16 nNameIdx = rNameMgr.InsertName(nTab, rTokData.mpScToken->GetIndex());
diff --git a/sc/source/filter/inc/tokstack.hxx b/sc/source/filter/inc/tokstack.hxx
index 9ceb2a4e9aa3..71ef978db958 100644
--- a/sc/source/filter/inc/tokstack.hxx
+++ b/sc/source/filter/inc/tokstack.hxx
@@ -126,7 +126,7 @@ private:
         struct RangeName
         {
             sal_uInt16 mnIndex;
-            bool mbGlobal;
+            sal_Int16  mnSheet;
         };
         ::std::vector<RangeName> maRangeNames;
 
@@ -205,7 +205,7 @@ public:
                                         // 4 externals (e.g. AddIns, Macros...)
         const TokenId               StoreNlf( const ScSingleRefData& rTr );
         const TokenId               StoreMatrix();
-        const TokenId               StoreName( sal_uInt16 nIndex, bool bGlobal );
+        const TokenId               StoreName( sal_uInt16 nIndex, sal_Int16 nSheet );
         const TokenId               StoreExtName( sal_uInt16 nFileId, const OUString& rName );
         const TokenId               StoreExtRef( sal_uInt16 nFileId, const OUString& rTabName, const ScSingleRefData& rRef );
         const TokenId               StoreExtRef( sal_uInt16 nFileId, const OUString& rTabName, const ScComplexRefData& rRef );
diff --git a/sc/source/ui/unoobj/tokenuno.cxx b/sc/source/ui/unoobj/tokenuno.cxx
index 6e769d03bd61..edea996935c6 100644
--- a/sc/source/ui/unoobj/tokenuno.cxx
+++ b/sc/source/ui/unoobj/tokenuno.cxx
@@ -424,7 +424,8 @@ bool ScTokenConversion::ConvertToTokenSequence( const ScDocument& rDoc,
                     {
                         sheet::NameToken aNameToken;
                         aNameToken.Index = static_cast<sal_Int32>( rToken.GetIndex() );
-                        aNameToken.Global = rToken.IsGlobal();
+                        /* FIXME: we need a new token with sheet number */
+                        aNameToken.Global = (rToken.GetSheet() < 0);
                         rAPI.Data <<= aNameToken;
                     }
                     break;