Resolves: tdf#120013 tdf#123714 tdf#123736 shared formula group split

 This is a combination of 7 commits.

Resolves: tdf#123714 tdf#123736 all split formula groups; tdf#120013 related

Add all split shared formula groups to regrouping and mark for
listening, even if the references had not be to adjusted.

This partly also resolves tdf#120013 but there's more to that, a
remaining partial group is not updated.

Reviewed-on: https://gerrit.libreoffice.org/68951
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
(cherry picked from commit 7fdc5df36f5b50e0629405a47ff3d5765fcfeb93)

Resolves: tdf#120013 tdf#123714 split-off group or single cell needs listening

Reviewed-on: https://gerrit.libreoffice.org/69066
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
(cherry picked from commit 5c27a048658afcd2f78ef4d7e6c7128554ed3f4c)

Resolves: tdf#123736 re-establish listeners for unshared formula groups

... when replacing one of their formula cells, with another
formula cell or any other cell, passing through
DetachFormulaCell()

Reviewed-on: https://gerrit.libreoffice.org/69221
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
(cherry picked from commit 1634a6d926c6cfd8fe92be1f3ae6083d2fb80f5e)

In case of sc::NoListening only SetNeedsListeningGroup(), tdf#123736 follow-up

Reviewed-on: https://gerrit.libreoffice.org/69303
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
(cherry picked from commit 6160025b27e97841321be29863bb1efd8c194a5f)

Related: tdf#123736 re-establish listeners also for vector unsharing

... via DetachFormulaCells()

Reviewed-on: https://gerrit.libreoffice.org/69320
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
(cherry picked from commit 564d0d145cf9c164ea9c717b4b2113fd971fa0af)

Reget position_type as the block or type may have changed, tdf#123736 related

Reviewed-on: https://gerrit.libreoffice.org/69358
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
(cherry picked from commit e5de84e73ffbaa1a45ab787750f5997582bbfa49)

Listening when grouping in ScColumn::AttachNewFormulaCells(), (tdf#123736)

Not directly related to tdf#123736 but similar approach.
Setting a vector with ScColumn::SetFormulaCells() is currently
only done for Undo documents, but implementation provided
listening as only single cell listening for not-undo/clip
documents, which wouldn't work if actually used in grouping
context. The upcoming unit tests will use SetFormulaCells() for
checks.

Reviewed-on: https://gerrit.libreoffice.org/69371
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
(cherry picked from commit de024e572dd7a588f82b84c68daa2051ec6b20e9)

c511f618e9dde2288491c01cfcd889970fd6e4e5
d527307ef5278d87345c6dbfab9d05cb490dfe6c
3e5a5334dfe11002b526f942463626b62efbc340
a57308495a06e0df612eb1610b5f387d6b60ce08
2faf3e74d95cb1f3088f20cbb7de7ba965a6de21
990e6a5d6e1efcf70a2661b3a9a39c37d9e4c2e6

Change-Id: If6d1fef7e545017232a1b7e29b4d60dd58775e88
Reviewed-on: https://gerrit.libreoffice.org/69554
Tested-by: Jenkins
Reviewed-by: Dennis Francis <dennis.francis@collabora.com>
Reviewed-by: Katarina Behrens <Katarina.Behrens@cib.de>

7 files changed, 358 insertions, 69 deletions

diff --git a/sc/inc/column.hxx b/sc/inc/column.hxx
index b1e890a6347c..eec55c3b6143 100644
--- a/sc/inc/column.hxx
+++ b/sc/inc/column.hxx
@@ -636,13 +636,23 @@ public:
     /**
      * Detach a formula cell that's about to be deleted, or removed from
      * document storage (if that ever happens).
+     *
+     * @param rNewSharedRows collects possible new shared row ranges (top and
+     *        bottom of shared or remaining single twice) resulting from
+     *        unsharing to reestablish listeners on.
      */
-    void DetachFormulaCell( const sc::CellStoreType::position_type& aPos, ScFormulaCell& rCell );
+    void DetachFormulaCell( const sc::CellStoreType::position_type& aPos, ScFormulaCell& rCell,
+                            std::vector<SCROW>& rNewSharedRows );
+
+    /** Re-establish listeners on unshared formula groups */
+    void StartListeningUnshared( const std::vector<SCROW>& rNewSharedRows );
 
-    void DetachFormulaCells( const sc::CellStoreType::position_type& aPos, size_t nLength );
+    void DetachFormulaCells( const sc::CellStoreType::position_type& aPos, size_t nLength,
+                             std::vector<SCROW>* pNewSharedRows );
 
     void AttachFormulaCells( sc::StartListeningContext& rCxt, SCROW nRow1, SCROW nRow2 );
-    void DetachFormulaCells( sc::EndListeningContext& rCxt, SCROW nRow1, SCROW nRow2 );
+    void DetachFormulaCells( sc::EndListeningContext& rCxt, SCROW nRow1, SCROW nRow2,
+                             std::vector<SCROW>* pNewSharedRows );
 
     /**
      * Regroup formula cells for the entire column.
@@ -692,18 +702,23 @@ public:
     bool        ReservePatternCount( SCSIZE nReserve );
 private:
 
-    sc::CellStoreType::iterator GetPositionToInsert( SCROW nRow );
-    sc::CellStoreType::iterator GetPositionToInsert( const sc::CellStoreType::iterator& it, SCROW nRow );
+    sc::CellStoreType::iterator GetPositionToInsert( SCROW nRow, std::vector<SCROW>& rNewSharedRows );
+    sc::CellStoreType::iterator GetPositionToInsert( const sc::CellStoreType::iterator& it, SCROW nRow,
+                                                     std::vector<SCROW>& rNewSharedRows );
 
     void AttachNewFormulaCell(
         const sc::CellStoreType::iterator& itPos, SCROW nRow, ScFormulaCell& rCell,
+        const std::vector<SCROW>& rNewSharedRows,
         bool bJoin = true, sc::StartListeningType eListenType = sc::SingleCellListening );
 
     void AttachNewFormulaCell(
         const sc::CellStoreType::position_type& aPos, ScFormulaCell& rCell,
+        const std::vector<SCROW>& rNewSharedRows,
         bool bJoin = true, sc::StartListeningType eListenType = sc::SingleCellListening );
 
-    void AttachNewFormulaCells( const sc::CellStoreType::position_type& aPos, size_t nLength );
+    void AttachNewFormulaCells( const sc::CellStoreType::position_type& aPos, size_t nLength,
+                                std::vector<SCROW>& rNewSharedRows );
+
     void BroadcastNewCell( SCROW nRow );
     bool UpdateScriptType( sc::CellTextAttr& rAttr, SCROW nRow, sc::CellStoreType::iterator& itr );
 
diff --git a/sc/inc/sharedformula.hxx b/sc/inc/sharedformula.hxx
index f4c8ab018532..b7c9577b2dfa 100644
--- a/sc/inc/sharedformula.hxx
+++ b/sc/inc/sharedformula.hxx
@@ -57,6 +57,9 @@ public:
         }
     }
 
+    /** Get shared formula top cell from position, if any, else nullptr. */
+    static const ScFormulaCell* getSharedTopFormulaCell(const CellStoreType::position_type& aPos);
+
     /**
      * Split existing shared formula range at specified position. The cell at
      * specified position becomes the top cell of the lower shared formula
@@ -65,8 +68,11 @@ public:
      *
      * @param aPos position of cell to examine.
      * @param pCxt context to be used, if any, may be nullptr.
+     *
+     * @return TRUE if there indeed was a split, else FALSE (e.g. split
+     *         position was top or bottom cell or no formula group).
      */
-    static void splitFormulaCellGroup(const CellStoreType::position_type& aPos, sc::EndListeningContext* pCxt);
+    static bool splitFormulaCellGroup(const CellStoreType::position_type& aPos, sc::EndListeningContext* pCxt);
 
     /**
      * Split existing shared formula ranges at specified row positions.
@@ -75,8 +81,11 @@ public:
      * @param rBounds row positions at which to split existing shared formula
      *                ranges. Note that this method will directly modify this
      *                parameter to sort and remove duplicates.
+     *
+     * @return TRUE if there indeed was a split, else FALSE (e.g. split
+     *         positions were only top or bottom cells or no formula group).
      */
-    static void splitFormulaCellGroups(CellStoreType& rCells, std::vector<SCROW>& rBounds);
+    static bool splitFormulaCellGroups(CellStoreType& rCells, std::vector<SCROW>& rBounds);
 
     /**
      * See if two specified adjacent formula cells can be merged, and if they
diff --git a/sc/source/core/data/column.cxx b/sc/source/core/data/column.cxx
index a7119c52dc64..12c3df5b827a 100644
--- a/sc/source/core/data/column.cxx
+++ b/sc/source/core/data/column.cxx
@@ -2475,7 +2475,7 @@ bool ScColumn::UpdateReference( sc::RefUpdateContext& rCxt, ScDocument* pUndoDoc
     }
 
     // Do the actual splitting.
-    sc::SharedFormulaUtil::splitFormulaCellGroups(maCells, aBounds);
+    const bool bSplit = sc::SharedFormulaUtil::splitFormulaCellGroups(maCells, aBounds);
 
     // Collect all formula groups.
     std::vector<sc::FormulaGroupEntry> aGroups = GetFormulaGroupEntries();
@@ -2483,7 +2483,7 @@ bool ScColumn::UpdateReference( sc::RefUpdateContext& rCxt, ScDocument* pUndoDoc
     // Process all collected formula groups.
     UpdateRefOnNonCopy aHandler(nCol, nTab, &rCxt, pUndoDoc);
     aHandler = std::for_each(aGroups.begin(), aGroups.end(), aHandler);
-    if (aHandler.isUpdated())
+    if (bSplit || aHandler.isUpdated())
         rCxt.maRegroupCols.set(nTab, nCol);
 
     return aHandler.isUpdated();
diff --git a/sc/source/core/data/column3.cxx b/sc/source/core/data/column3.cxx
index 295c93d03c8a..b210c63d2366 100644
--- a/sc/source/core/data/column3.cxx
+++ b/sc/source/core/data/column3.cxx
@@ -257,9 +257,9 @@ void ScColumn::DeleteRow( SCROW nStartRow, SCSIZE nSize, std::vector<ScAddress>*
     CellStorageModified();
 }
 
-sc::CellStoreType::iterator ScColumn::GetPositionToInsert( SCROW nRow )
+sc::CellStoreType::iterator ScColumn::GetPositionToInsert( SCROW nRow, std::vector<SCROW>& rNewSharedRows )
 {
-    return GetPositionToInsert(maCells.begin(), nRow);
+    return GetPositionToInsert(maCells.begin(), nRow, rNewSharedRows);
 }
 
 void ScColumn::JoinNewFormulaCell(
@@ -283,15 +283,74 @@ void ScColumn::JoinNewFormulaCell(
 }
 
 void ScColumn::DetachFormulaCell(
-    const sc::CellStoreType::position_type& aPos, ScFormulaCell& rCell )
+    const sc::CellStoreType::position_type& aPos, ScFormulaCell& rCell, std::vector<SCROW>& rNewSharedRows )
 {
     if (!GetDoc()->IsClipOrUndo())
+    {
+#if USE_FORMULA_GROUP_LISTENER
+        if (rCell.IsShared() && rCell.GetSharedLength() > 1)
+        {
+            // Record new spans (shared or remaining single) that will result
+            // from unsharing to reestablish listeners.
+            // Same cases as in unshareFormulaCell().
+            // XXX NOTE: this is not part of unshareFormulaCell() because that
+            // is called in other contexts as well, for which passing and
+            // determining the rows vector would be superfluous. If that was
+            // needed, move it there.
+            const SCROW nSharedTopRow = rCell.GetSharedTopRow();
+            const SCROW nSharedLength = rCell.GetSharedLength();
+            if (rCell.aPos.Row() == nSharedTopRow)
+            {
+                // Top cell.
+                // Next row will be new shared top or single cell.
+                rNewSharedRows.push_back( nSharedTopRow + 1);
+                rNewSharedRows.push_back( nSharedTopRow + nSharedLength - 1);
+            }
+            else if (rCell.aPos.Row() == nSharedTopRow + nSharedLength - 1)
+            {
+                // Bottom cell.
+                // Current shared top row will be new shared top again or
+                // single cell.
+                rNewSharedRows.push_back( nSharedTopRow);
+                rNewSharedRows.push_back( rCell.aPos.Row() - 1);
+            }
+            else
+            {
+                // Some mid cell.
+                // Current shared top row will be new shared top again or
+                // single cell, plus a new shared top below or single cell.
+                rNewSharedRows.push_back( nSharedTopRow);
+                rNewSharedRows.push_back( rCell.aPos.Row() - 1);
+                rNewSharedRows.push_back( rCell.aPos.Row() + 1);
+                rNewSharedRows.push_back( nSharedTopRow + nSharedLength - 1);
+            }
+        }
+#endif
+
         // Have the dying formula cell stop listening.
+        // If in a shared formula group this ends the group listening.
         rCell.EndListeningTo(GetDoc());
+    }
 
     sc::SharedFormulaUtil::unshareFormulaCell(aPos, rCell);
 }
 
+void ScColumn::StartListeningUnshared( const std::vector<SCROW>& rNewSharedRows )
+{
+    assert(rNewSharedRows.empty() || rNewSharedRows.size() == 2 || rNewSharedRows.size() == 4);
+    ScDocument* pDoc = GetDoc();
+    if (!rNewSharedRows.empty() /* && !pDoc->IsDelayedFormulaGrouping() N/A in backport */)
+    {
+        std::shared_ptr<sc::ColumnBlockPositionSet> pPosSet(new sc::ColumnBlockPositionSet(*pDoc));
+        sc::StartListeningContext aStartCxt(*pDoc, pPosSet);
+        sc::EndListeningContext aEndCxt(*pDoc, pPosSet);
+        if (rNewSharedRows.size() >= 2)
+            StartListeningFormulaCells(aStartCxt, aEndCxt, rNewSharedRows[0], rNewSharedRows[1]);
+        if (rNewSharedRows.size() >= 4)
+            StartListeningFormulaCells(aStartCxt, aEndCxt, rNewSharedRows[2], rNewSharedRows[3]);
+    }
+}
+
 namespace {
 
 class AttachFormulaCellsHandler
@@ -329,14 +388,59 @@ public:
 }
 
 void ScColumn::DetachFormulaCells(
-    const sc::CellStoreType::position_type& aPos, size_t nLength )
+    const sc::CellStoreType::position_type& aPos, size_t nLength, std::vector<SCROW>* pNewSharedRows )
 {
+    const size_t nRow = aPos.first->position + aPos.second;
+    const size_t nNextTopRow = nRow + nLength; // start row of next formula group.
+
+    bool bLowerSplitOff = false;
+    if (pNewSharedRows && !GetDoc()->IsClipOrUndo())
+    {
+        const ScFormulaCell* pFC = sc::SharedFormulaUtil::getSharedTopFormulaCell(aPos);
+        if (pFC)
+        {
+            const SCROW nTopRow = pFC->GetSharedTopRow();
+            const SCROW nBotRow = nTopRow + pFC->GetSharedLength() - 1;
+            // nTopRow <= nRow <= nBotRow, because otherwise pFC would not exist.
+            if (nTopRow < static_cast<SCROW>(nRow))
+            {
+                // Upper part will be split off.
+                pNewSharedRows->push_back(nTopRow);
+                pNewSharedRows->push_back(nRow - 1);
+            }
+            if (static_cast<SCROW>(nNextTopRow) <= nBotRow)
+            {
+                // Lower part will be split off.
+                pNewSharedRows->push_back(nNextTopRow);
+                pNewSharedRows->push_back(nBotRow);
+                bLowerSplitOff = true;
+            }
+        }
+    }
+
     // Split formula grouping at the top and bottom boundaries.
     sc::SharedFormulaUtil::splitFormulaCellGroup(aPos, nullptr);
-    size_t nRow = aPos.first->position + aPos.second;
-    size_t nNextTopRow = nRow + nLength; // start row of next formula group.
-    if (ValidRow(nNextTopRow))
+
+    if (nLength > 0 && ValidRow(nNextTopRow))
     {
+        if (pNewSharedRows && !bLowerSplitOff && !GetDoc()->IsClipOrUndo())
+        {
+            sc::CellStoreType::position_type aPos2 = maCells.position(aPos.first, nNextTopRow-1);
+            const ScFormulaCell* pFC = sc::SharedFormulaUtil::getSharedTopFormulaCell(aPos2);
+            if (pFC)
+            {
+                const SCROW nTopRow = pFC->GetSharedTopRow();
+                const SCROW nBotRow = nTopRow + pFC->GetSharedLength() - 1;
+                // nRow < nTopRow < nNextTopRow <= nBotRow
+                if (static_cast<SCROW>(nNextTopRow) <= nBotRow)
+                {
+                    // Lower part will be split off.
+                    pNewSharedRows->push_back(nNextTopRow);
+                    pNewSharedRows->push_back(nBotRow);
+                }
+            }
+        }
+
         sc::CellStoreType::position_type aPos2 = maCells.position(aPos.first, nNextTopRow);
         sc::SharedFormulaUtil::splitFormulaCellGroup(aPos2, nullptr);
     }
@@ -367,15 +471,59 @@ void ScColumn::AttachFormulaCells( sc::StartListeningContext& rCxt, SCROW nRow1,
     sc::ProcessFormula(it, maCells, nRow1, nRow2, aFunc);
 }
 
-void ScColumn::DetachFormulaCells( sc::EndListeningContext& rCxt, SCROW nRow1, SCROW nRow2 )
+void ScColumn::DetachFormulaCells( sc::EndListeningContext& rCxt, SCROW nRow1, SCROW nRow2,
+        std::vector<SCROW>* pNewSharedRows )
 {
     sc::CellStoreType::position_type aPos = maCells.position(nRow1);
     sc::CellStoreType::iterator it = aPos.first;
 
+    bool bLowerSplitOff = false;
+    if (pNewSharedRows && !GetDoc()->IsClipOrUndo())
+    {
+        const ScFormulaCell* pFC = sc::SharedFormulaUtil::getSharedTopFormulaCell(aPos);
+        if (pFC)
+        {
+            const SCROW nTopRow = pFC->GetSharedTopRow();
+            const SCROW nBotRow = nTopRow + pFC->GetSharedLength() - 1;
+            // nTopRow <= nRow1 <= nBotRow, because otherwise pFC would not exist.
+            if (nTopRow < nRow1)
+            {
+                // Upper part will be split off.
+                pNewSharedRows->push_back(nTopRow);
+                pNewSharedRows->push_back(nRow1 - 1);
+            }
+            if (nRow2 < nBotRow)
+            {
+                // Lower part will be split off.
+                pNewSharedRows->push_back(nRow2 + 1);
+                pNewSharedRows->push_back(nBotRow);
+                bLowerSplitOff = true;
+            }
+        }
+    }
+
     // Split formula grouping at the top and bottom boundaries.
     sc::SharedFormulaUtil::splitFormulaCellGroup(aPos, &rCxt);
     if (ValidRow(nRow2+1))
     {
+        if (pNewSharedRows && !bLowerSplitOff && !GetDoc()->IsClipOrUndo())
+        {
+            sc::CellStoreType::position_type aPos2 = maCells.position(aPos.first, nRow2);
+            const ScFormulaCell* pFC = sc::SharedFormulaUtil::getSharedTopFormulaCell(aPos2);
+            if (pFC)
+            {
+                const SCROW nTopRow = pFC->GetSharedTopRow();
+                const SCROW nBotRow = nTopRow + pFC->GetSharedLength() - 1;
+                // nRow1 < nTopRow <= nRow2 < nBotRow
+                if (nRow2 < nBotRow)
+                {
+                    // Lower part will be split off.
+                    pNewSharedRows->push_back(nRow2 + 1);
+                    pNewSharedRows->push_back(nBotRow);
+                }
+            }
+        }
+
         aPos = maCells.position(it, nRow2+1);
         sc::SharedFormulaUtil::splitFormulaCellGroup(aPos, &rCxt);
     }
@@ -387,7 +535,8 @@ void ScColumn::DetachFormulaCells( sc::EndListeningContext& rCxt, SCROW nRow1, S
     sc::ProcessFormula(it, maCells, nRow1, nRow2, aFunc);
 }
 
-sc::CellStoreType::iterator ScColumn::GetPositionToInsert( const sc::CellStoreType::iterator& it, SCROW nRow )
+sc::CellStoreType::iterator ScColumn::GetPositionToInsert( const sc::CellStoreType::iterator& it, SCROW nRow,
+        std::vector<SCROW>& rNewSharedRows )
 {
     // See if we are overwriting an existing formula cell.
     sc::CellStoreType::position_type aPos = maCells.position(it, nRow);
@@ -395,7 +544,7 @@ sc::CellStoreType::iterator ScColumn::GetPositionToInsert( const sc::CellStoreTy
     if (itRet->type == sc::element_type_formula)
     {
         ScFormulaCell& rCell = *sc::formula_block::at(*itRet->data, aPos.second);
-        DetachFormulaCell(aPos, rCell);
+        DetachFormulaCell(aPos, rCell, rNewSharedRows);
     }
 
     return itRet;
@@ -403,13 +552,15 @@ sc::CellStoreType::iterator ScColumn::GetPositionToInsert( const sc::CellStoreTy
 
 void ScColumn::AttachNewFormulaCell(
     const sc::CellStoreType::iterator& itPos, SCROW nRow, ScFormulaCell& rCell,
+    const std::vector<SCROW>& rNewSharedRows,
     bool bJoin, sc::StartListeningType eListenType )
 {
-    AttachNewFormulaCell(maCells.position(itPos, nRow), rCell, bJoin, eListenType);
+    AttachNewFormulaCell(maCells.position(itPos, nRow), rCell, rNewSharedRows, bJoin, eListenType);
 }
 
 void ScColumn::AttachNewFormulaCell(
     const sc::CellStoreType::position_type& aPos, ScFormulaCell& rCell,
+    const std::vector<SCROW>& rNewSharedRows,
     bool bJoin, sc::StartListeningType eListenType )
 {
     if (bJoin)
@@ -432,24 +583,50 @@ void ScColumn::AttachNewFormulaCell(
             std::shared_ptr<sc::ColumnBlockPositionSet> pPosSet(new sc::ColumnBlockPositionSet(*pDocument));
             sc::StartListeningContext aStartCxt(*pDocument, pPosSet);
             sc::EndListeningContext aEndCxt(*pDocument, pPosSet);
-            SCROW nRow = aPos.first->position + aPos.second;
-            StartListeningFormulaCells(aStartCxt, aEndCxt, nRow, nRow);
+            SCROW nStartRow, nEndRow;
+            nStartRow = nEndRow = aPos.first->position + aPos.second;
+            for (const SCROW nR : rNewSharedRows)
+            {
+                if (nStartRow > nR)
+                    nStartRow = nR;
+                if (nEndRow < nR)
+                    nEndRow = nR;
+            }
+            StartListeningFormulaCells(aStartCxt, aEndCxt, nStartRow, nEndRow);
         }
         break;
         case sc::SingleCellListening:
             rCell.StartListeningTo(pDocument);
+            StartListeningUnshared( rNewSharedRows);
         break;
         case sc::NoListening:
         default:
-            ;
-
+            if (!rNewSharedRows.empty())
+            {
+                assert(rNewSharedRows.size() == 2 || rNewSharedRows.size() == 4);
+                // Calling SetNeedsListeningGroup() with a top row sets it to
+                // all affected formula cells of that group.
+                const ScFormulaCell* pFC = GetFormulaCell( rNewSharedRows[0]);
+                assert(pFC);    // that *is* supposed to be a top row
+                if (pFC && !pFC->NeedsListening())
+                    SetNeedsListeningGroup( rNewSharedRows[0]);
+                if (rNewSharedRows.size() > 2)
+                {
+                    pFC = GetFormulaCell( rNewSharedRows[2]);
+                    assert(pFC);    // that *is* supposed to be a top row
+                    if (pFC && !pFC->NeedsListening())
+                        SetNeedsListeningGroup( rNewSharedRows[2]);
+                }
+            }
+        break;
     }
 
     if (!pDocument->IsCalcingAfterLoad())
         rCell.SetDirty();
 }
 
-void ScColumn::AttachNewFormulaCells( const sc::CellStoreType::position_type& aPos, size_t nLength )
+void ScColumn::AttachNewFormulaCells( const sc::CellStoreType::position_type& aPos, size_t nLength,
+        std::vector<SCROW>& rNewSharedRows )
 {
     // Make sure the whole length consists of formula cells.
     if (aPos.first->type != sc::element_type_formula)
@@ -460,26 +637,61 @@ void ScColumn::AttachNewFormulaCells( const sc::CellStoreType::position_type& aP
         return;
 
     // Join the top and bottom cells only.
-    ScFormulaCell* pCell = sc::formula_block::at(*aPos.first->data, aPos.second);
-    JoinNewFormulaCell(aPos, *pCell);
+    ScFormulaCell* pCell1 = sc::formula_block::at(*aPos.first->data, aPos.second);
+    JoinNewFormulaCell(aPos, *pCell1);
 
     sc::CellStoreType::position_type aPosLast = aPos;
     aPosLast.second += nLength - 1;
-    pCell = sc::formula_block::at(*aPosLast.first->data, aPosLast.second);
-    JoinNewFormulaCell(aPosLast, *pCell);
+    ScFormulaCell* pCell2 = sc::formula_block::at(*aPosLast.first->data, aPosLast.second);
+    JoinNewFormulaCell(aPosLast, *pCell2);
 
     ScDocument* pDocument = GetDoc();
     if (!pDocument->IsClipOrUndo() && !pDocument->IsInsertingFromOtherDoc())
     {
+        const bool bShared = pCell1->IsShared() || pCell2->IsShared();
+        if (bShared)
+        {
+            const SCROW nTopRow = (pCell1->IsShared() ? pCell1->GetSharedTopRow() : pCell1->aPos.Row());
+            const SCROW nBotRow = (pCell2->IsShared() ?
+                    pCell2->GetSharedTopRow() + pCell2->GetSharedLength() - 1 : pCell2->aPos.Row());
+            if (rNewSharedRows.empty())
+            {
+                rNewSharedRows.push_back( nTopRow);
+                rNewSharedRows.push_back( nBotRow);
+            }
+            else if (rNewSharedRows.size() == 2)
+            {
+                // Combine into one span.
+                if (rNewSharedRows[0] > nTopRow)
+                    rNewSharedRows[0] = nTopRow;
+                if (rNewSharedRows[1] < nBotRow)
+                    rNewSharedRows[1] = nBotRow;
+            }
+            else if (rNewSharedRows.size() == 4)
+            {
+                // Merge into one span.
+                // The original two spans are ordered from top to bottom.
+                std::vector<SCROW> aRows(2);
+                aRows[0] = std::min( rNewSharedRows[0], nTopRow);
+                aRows[1] = std::max( rNewSharedRows[3], nBotRow);
+                rNewSharedRows.swap( aRows);
+            }
+            else
+            {
+                assert(!"rNewSharedRows?");
+            }
+        }
+        StartListeningUnshared( rNewSharedRows);
+
         sc::StartListeningContext aCxt(*pDocument);
         ScFormulaCell** pp = &sc::formula_block::at(*aPos.first->data, aPos.second);
         ScFormulaCell** ppEnd = pp + nLength;
         for (; pp != ppEnd; ++pp)
         {
-            pCell = *pp;
-            pCell->StartListeningTo(aCxt);
+            if (!bShared)
+                (*pp)->StartListeningTo(aCxt);
             if (!pDocument->IsCalcingAfterLoad())
-                pCell->SetDirty();
+                (*pp)->SetDirty();
         }
     }
 }
@@ -1517,7 +1729,7 @@ public:
 
         // Stop all formula cells in the destination range first.
         sc::CellStoreType::position_type aPos = rDestCells.position(mrBlockPos.miCellPos, mnRowOffset);
-        mrDestColumn.DetachFormulaCells(aPos, maNewCells.size());
+        mrDestColumn.DetachFormulaCells(aPos, maNewCells.size(), nullptr);
 
         // Move the new cells to the destination range.
         sc::CellStoreType::iterator& itDestPos = mrBlockPos.miCellPos;
@@ -1876,24 +2088,30 @@ bool ScColumn::SetString( SCROW nRow, SCTAB nTabP, const OUString& rString,
 void ScColumn::SetEditText( SCROW nRow, std::unique_ptr<EditTextObject> pEditText )
 {
     pEditText->NormalizeString(GetDoc()->GetSharedStringPool());
-    sc::CellStoreType::iterator it = GetPositionToInsert(nRow);
+    std::vector<SCROW> aNewSharedRows;
+    sc::CellStoreType::iterator it = GetPositionToInsert(nRow, aNewSharedRows);
     maCells.set(it, nRow, pEditText.release());
     maCellTextAttrs.set(nRow, sc::CellTextAttr());
     CellStorageModified();
 
+    StartListeningUnshared( aNewSharedRows);
+
     BroadcastNewCell(nRow);
 }
 
 void ScColumn::SetEditText( sc::ColumnBlockPosition& rBlockPos, SCROW nRow, std::unique_ptr<EditTextObject> pEditText )
 {
     pEditText->NormalizeString(GetDoc()->GetSharedStringPool());
-    rBlockPos.miCellPos = GetPositionToInsert(rBlockPos.miCellPos, nRow);
+    std::vector<SCROW> aNewSharedRows;
+    rBlockPos.miCellPos = GetPositionToInsert(rBlockPos.miCellPos, nRow, aNewSharedRows);
     rBlockPos.miCellPos = maCells.set(rBlockPos.miCellPos, nRow, pEditText.release());
     rBlockPos.miCellTextAttrPos = maCellTextAttrs.set(
         rBlockPos.miCellTextAttrPos, nRow, sc::CellTextAttr());
 
     CellStorageModified();
 
+    StartListeningUnshared( aNewSharedRows);
+
     BroadcastNewCell(nRow);
 }
 
@@ -1933,7 +2151,8 @@ void ScColumn::SetFormula( SCROW nRow, const ScTokenArray& rArray, formula::Form
 {
     ScAddress aPos(nCol, nRow, nTab);
 
-    sc::CellStoreType::iterator it = GetPositionToInsert(nRow);
+    std::vector<SCROW> aNewSharedRows;
+    sc::CellStoreType::iterator it = GetPositionToInsert(nRow, aNewSharedRows);
     ScFormulaCell* pCell = new ScFormulaCell(GetDoc(), aPos, rArray, eGram);
     sal_uInt32 nCellFormat = GetNumberFormat(GetDoc()->GetNonThreadedContext(), nRow);
     if( (nCellFormat % SV_COUNTRY_LANGUAGE_OFFSET) == 0)
@@ -1943,14 +2162,15 @@ void ScColumn::SetFormula( SCROW nRow, const ScTokenArray& rArray, formula::Form
 
     CellStorageModified();
 
-    AttachNewFormulaCell(it, nRow, *pCell);
+    AttachNewFormulaCell(it, nRow, *pCell, aNewSharedRows);
 }
 
 void ScColumn::SetFormula( SCROW nRow, const OUString& rFormula, formula::FormulaGrammar::Grammar eGram )
 {
     ScAddress aPos(nCol, nRow, nTab);
 
-    sc::CellStoreType::iterator it = GetPositionToInsert(nRow);
+    std::vector<SCROW> aNewSharedRows;
+    sc::CellStoreType::iterator it = GetPositionToInsert(nRow, aNewSharedRows);
     ScFormulaCell* pCell = new ScFormulaCell(GetDoc(), aPos, rFormula, eGram);
     sal_uInt32 nCellFormat = GetNumberFormat(GetDoc()->GetNonThreadedContext(), nRow);
     if( (nCellFormat % SV_COUNTRY_LANGUAGE_OFFSET) == 0)
@@ -1960,14 +2180,15 @@ void ScColumn::SetFormula( SCROW nRow, const OUString& rFormula, formula::Formul
 
     CellStorageModified();
 
-    AttachNewFormulaCell(it, nRow, *pCell);
+    AttachNewFormulaCell(it, nRow, *pCell, aNewSharedRows);
 }
 
 ScFormulaCell* ScColumn::SetFormulaCell(
     SCROW nRow, ScFormulaCell* pCell, sc::StartListeningType eListenType,
     bool bInheritNumFormatIfNeeded )
 {
-    sc::CellStoreType::iterator it = GetPositionToInsert(nRow);
+    std::vector<SCROW> aNewSharedRows;
+    sc::CellStoreType::iterator it = GetPositionToInsert(nRow, aNewSharedRows);
     sal_uInt32 nCellFormat = GetNumberFormat(GetDoc()->GetNonThreadedContext(), nRow);
     if( (nCellFormat % SV_COUNTRY_LANGUAGE_OFFSET) == 0 && bInheritNumFormatIfNeeded )
         pCell->SetNeedNumberFormat(true);
@@ -1976,7 +2197,8 @@ ScFormulaCell* ScColumn::SetFormulaCell(
 
     CellStorageModified();
 
-    AttachNewFormulaCell(it, nRow, *pCell, true, eListenType);
+    AttachNewFormulaCell(it, nRow, *pCell, aNewSharedRows, true, eListenType);
+
     return pCell;
 }
 
@@ -1985,7 +2207,8 @@ void ScColumn::SetFormulaCell(
     sc::StartListeningType eListenType,
     bool bInheritNumFormatIfNeeded )
 {
-    rBlockPos.miCellPos = GetPositionToInsert(rBlockPos.miCellPos, nRow);
+    std::vector<SCROW> aNewSharedRows;
+    rBlockPos.miCellPos = GetPositionToInsert(rBlockPos.miCellPos, nRow, aNewSharedRows);
     sal_uInt32 nCellFormat = GetNumberFormat(GetDoc()->GetNonThreadedContext(), nRow);
     if( (nCellFormat % SV_COUNTRY_LANGUAGE_OFFSET) == 0 && bInheritNumFormatIfNeeded )
         pCell->SetNeedNumberFormat(true);
@@ -1995,7 +2218,7 @@ void ScColumn::SetFormulaCell(
 
     CellStorageModified();
 
-    AttachNewFormulaCell(rBlockPos.miCellPos, nRow, *pCell, true, eListenType);
+    AttachNewFormulaCell(rBlockPos.miCellPos, nRow, *pCell, aNewSharedRows, true, eListenType);
 }
 
 bool ScColumn::SetFormulaCells( SCROW nRow, std::vector<ScFormulaCell*>& rCells )
@@ -2010,7 +2233,8 @@ bool ScColumn::SetFormulaCells( SCROW nRow, std::vector<ScFormulaCell*>& rCells
     sc::CellStoreType::position_type aPos = maCells.position(nRow);
 
     // Detach all formula cells that will be overwritten.
-    DetachFormulaCells(aPos, rCells.size());
+    std::vector<SCROW> aNewSharedRows;
+    DetachFormulaCells(aPos, rCells.size(), &aNewSharedRows);
 
     if (!GetDoc()->IsClipOrUndo())
     {
@@ -2030,7 +2254,10 @@ bool ScColumn::SetFormulaCells( SCROW nRow, std::vector<ScFormulaCell*>& rCells
 
     CellStorageModified();
 
-    AttachNewFormulaCells(aPos, rCells.size());
+    // Reget position_type as the type may have changed to formula, block and
+    // block size changed, ...
+    aPos = maCells.position(nRow);
+    AttachNewFormulaCells(aPos, rCells.size(), aNewSharedRows);
 
     return true;
 }
@@ -2467,13 +2694,14 @@ void ScColumn::SetError( SCROW nRow, const FormulaError nError)
     ScFormulaCell* pCell = new ScFormulaCell(GetDoc(), ScAddress(nCol, nRow, nTab));
     pCell->SetErrCode(nError);
 
-    sc::CellStoreType::iterator it = GetPositionToInsert(nRow);
+    std::vector<SCROW> aNewSharedRows;
+    sc::CellStoreType::iterator it = GetPositionToInsert(nRow, aNewSharedRows);
     it = maCells.set(it, nRow, pCell);
     maCellTextAttrs.set(nRow, sc::CellTextAttr());
 
     CellStorageModified();
 
-    AttachNewFormulaCell(it, nRow, *pCell);
+    AttachNewFormulaCell(it, nRow, *pCell, aNewSharedRows);
 }
 
 void ScColumn::SetRawString( SCROW nRow, const OUString& rStr )
@@ -2493,12 +2721,15 @@ void ScColumn::SetRawString( SCROW nRow, const svl::SharedString& rStr )
     if (!ValidRow(nRow))
         return;
 
-    sc::CellStoreType::iterator it = GetPositionToInsert(nRow);
+    std::vector<SCROW> aNewSharedRows;
+    sc::CellStoreType::iterator it = GetPositionToInsert(nRow, aNewSharedRows);
     maCells.set(it, nRow, rStr);
     maCellTextAttrs.set(nRow, sc::CellTextAttr());
 
     CellStorageModified();
 
+    StartListeningUnshared( aNewSharedRows);
+
     BroadcastNewCell(nRow);
 }
 
@@ -2508,13 +2739,16 @@ void ScColumn::SetRawString(
     if (!ValidRow(nRow))
         return;
 
-    rBlockPos.miCellPos = GetPositionToInsert(rBlockPos.miCellPos, nRow);
+    std::vector<SCROW> aNewSharedRows;
+    rBlockPos.miCellPos = GetPositionToInsert(rBlockPos.miCellPos, nRow, aNewSharedRows);
     rBlockPos.miCellPos = maCells.set(rBlockPos.miCellPos, nRow, rStr);
     rBlockPos.miCellTextAttrPos = maCellTextAttrs.set(
         rBlockPos.miCellTextAttrPos, nRow, sc::CellTextAttr());
 
     CellStorageModified();
 
+    StartListeningUnshared( aNewSharedRows);
+
     if (bBroadcast)
         BroadcastNewCell(nRow);
 }
@@ -2524,12 +2758,15 @@ void ScColumn::SetValue( SCROW nRow, double fVal )
     if (!ValidRow(nRow))
         return;
 
-    sc::CellStoreType::iterator it = GetPositionToInsert(nRow);
+    std::vector<SCROW> aNewSharedRows;
+    sc::CellStoreType::iterator it = GetPositionToInsert(nRow, aNewSharedRows);
     maCells.set(it, nRow, fVal);
     maCellTextAttrs.set(nRow, sc::CellTextAttr());
 
     CellStorageModified();
 
+    StartListeningUnshared( aNewSharedRows);
+
     BroadcastNewCell(nRow);
 }
 
@@ -2539,13 +2776,16 @@ void ScColumn::SetValue(
     if (!ValidRow(nRow))
         return;
 
-    rBlockPos.miCellPos = GetPositionToInsert(rBlockPos.miCellPos, nRow);
+    std::vector<SCROW> aNewSharedRows;
+    rBlockPos.miCellPos = GetPositionToInsert(rBlockPos.miCellPos, nRow, aNewSharedRows);
     rBlockPos.miCellPos = maCells.set(rBlockPos.miCellPos, nRow, fVal);
     rBlockPos.miCellTextAttrPos = maCellTextAttrs.set(
         rBlockPos.miCellTextAttrPos, nRow, sc::CellTextAttr());
 
     CellStorageModified();
 
+    StartListeningUnshared( aNewSharedRows);
+
     if (bBroadcast)
         BroadcastNewCell(nRow);
 }
diff --git a/sc/source/core/data/column4.cxx b/sc/source/core/data/column4.cxx
index b324b2a2d928..e46a0d8a424b 100644
--- a/sc/source/core/data/column4.cxx
+++ b/sc/source/core/data/column4.cxx
@@ -316,7 +316,8 @@ void ScColumn::SetValues( const SCROW nRow, const std::vector<double>& rVals )
         return;
 
     sc::CellStoreType::position_type aPos = maCells.position(nRow);
-    DetachFormulaCells(aPos, rVals.size());
+    std::vector<SCROW> aNewSharedRows;
+    DetachFormulaCells(aPos, rVals.size(), &aNewSharedRows);
 
     maCells.set(nRow, rVals.begin(), rVals.end());
     std::vector<sc::CellTextAttr> aDefaults(rVals.size());
@@ -324,6 +325,8 @@ void ScColumn::SetValues( const SCROW nRow, const std::vector<double>& rVals )
 
     CellStorageModified();
 
+    StartListeningUnshared( aNewSharedRows);
+
     std::vector<SCROW> aRows;
     aRows.reserve(rVals.size());
     for (SCROW i = nRow; i <= nLastRow; ++i)
@@ -343,7 +346,7 @@ void ScColumn::TransferCellValuesTo( SCROW nRow, size_t nLen, sc::CellValues& rD
         return;
 
     sc::CellStoreType::position_type aPos = maCells.position(nRow);
-    DetachFormulaCells(aPos, nLen);
+    DetachFormulaCells(aPos, nLen, nullptr);
 
     rDest.transferFrom(*this, nRow, nLen);
 
@@ -368,7 +371,7 @@ void ScColumn::CopyCellValuesFrom( SCROW nRow, const sc::CellValues& rSrc )
         return;
 
     sc::CellStoreType::position_type aPos = maCells.position(nRow);
-    DetachFormulaCells(aPos, rSrc.size());
+    DetachFormulaCells(aPos, rSrc.size(), nullptr);
 
     rSrc.copyTo(*this, nRow);
 
@@ -444,7 +447,7 @@ void ScColumn::ConvertFormulaToValue(
         // No formula cells encountered.
         return;
 
-    DetachFormulaCells(rCxt, nRow1, nRow2);
+    DetachFormulaCells(rCxt, nRow1, nRow2, nullptr);
 
     // Undo storage to hold static values which will get swapped to the cell storage later.
     sc::CellValues aUndoCells;
diff --git a/sc/source/core/data/table2.cxx b/sc/source/core/data/table2.cxx
index 6608876a6ff4..37fd41890250 100644
--- a/sc/source/core/data/table2.cxx
+++ b/sc/source/core/data/table2.cxx
@@ -1088,7 +1088,7 @@ void ScTable::DetachFormulaCells(
     sc::EndListeningContext& rCxt, SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2 )
 {
     for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
-        aCol[nCol].DetachFormulaCells(rCxt, nRow1, nRow2);
+        aCol[nCol].DetachFormulaCells(rCxt, nRow1, nRow2, nullptr);
 }
 
 void ScTable::SetDirtyFromClip(
diff --git a/sc/source/core/tool/sharedformula.cxx b/sc/source/core/tool/sharedformula.cxx
index 913b2fe48bb9..e1f45a197962 100644
--- a/sc/source/core/tool/sharedformula.cxx
+++ b/sc/source/core/tool/sharedformula.cxx
@@ -17,28 +17,44 @@
 
 namespace sc {
 
-void SharedFormulaUtil::splitFormulaCellGroup(const CellStoreType::position_type& aPos, sc::EndListeningContext* pCxt)
+const ScFormulaCell* SharedFormulaUtil::getSharedTopFormulaCell(const CellStoreType::position_type& aPos)
+{
+    if (aPos.first->type != sc::element_type_formula)
+        // Not a formula cell block.
+        return nullptr;
+
+    sc::formula_block::iterator it = sc::formula_block::begin(*aPos.first->data);
+    std::advance(it, aPos.second);
+    const ScFormulaCell* pCell = *it;
+    if (!pCell->IsShared())
+        // Not a shared formula.
+        return nullptr;
+
+    return pCell->GetCellGroup()->mpTopCell;
+}
+
+bool SharedFormulaUtil::splitFormulaCellGroup(const CellStoreType::position_type& aPos, sc::EndListeningContext* pCxt)
 {
     SCROW nRow = aPos.first->position + aPos.second;
 
     if (aPos.first->type != sc::element_type_formula)
         // Not a formula cell block.
-        return;
+        return false;
 
     if (aPos.second == 0)
         // Split position coincides with the block border. Nothing to do.
-        return;
+        return false;
 
     sc::formula_block::iterator it = sc::formula_block::begin(*aPos.first->data);
     std::advance(it, aPos.second);
     ScFormulaCell& rTop = **it;
     if (!rTop.IsShared())
         // Not a shared formula.
-        return;
+        return false;
 
     if (nRow == rTop.GetSharedTopRow())
         // Already the top cell of a shared group.
-        return;
+        return false;
 
     ScFormulaCellGroupRef xGroup = rTop.GetCellGroup();
 
@@ -71,6 +87,9 @@ void SharedFormulaUtil::splitFormulaCellGroup(const CellStoreType::position_type
         else
             rPrevTop.EndListeningTo( rPrevTop.GetDocument(), nullptr, ScAddress( ScAddress::UNINITIALIZED));
         rPrevTop.SetNeedsListening(true);
+
+        // The new group or remaining single cell needs a new listening.
+        rTop.SetNeedsListening(true);
     }
 #endif
 
@@ -97,12 +116,14 @@ void SharedFormulaUtil::splitFormulaCellGroup(const CellStoreType::position_type
         ScFormulaCell& rCell = **it;
         rCell.SetCellGroup(xGroup2);
     }
+
+    return true;
 }
 
-void SharedFormulaUtil::splitFormulaCellGroups(CellStoreType& rCells, std::vector<SCROW>& rBounds)
+bool SharedFormulaUtil::splitFormulaCellGroups(CellStoreType& rCells, std::vector<SCROW>& rBounds)
 {
     if (rBounds.empty())
-        return;
+        return false;
 
     // Sort and remove duplicates.
     std::sort(rBounds.begin(), rBounds.end());
@@ -113,9 +134,9 @@ void SharedFormulaUtil::splitFormulaCellGroups(CellStoreType& rCells, std::vecto
     SCROW nRow = *it;
     CellStoreType::position_type aPos = rCells.position(nRow);
     if (aPos.first == rCells.end())
-        return;
+        return false;
 
-    splitFormulaCellGroup(aPos, nullptr);
+    bool bSplit = splitFormulaCellGroup(aPos, nullptr);
     std::vector<SCROW>::iterator itEnd = rBounds.end();
     for (++it; it != itEnd; ++it)
     {
@@ -124,10 +145,11 @@ void SharedFormulaUtil::splitFormulaCellGroups(CellStoreType& rCells, std::vecto
         {
             aPos = rCells.position(aPos.first, nRow);
             if (aPos.first == rCells.end())
-                return;
-            splitFormulaCellGroup(aPos, nullptr);
+                return bSplit;
+            bSplit |= splitFormulaCellGroup(aPos, nullptr);
         }
     }
+    return bSplit;
 }
 
 bool SharedFormulaUtil::joinFormulaCells(
@@ -235,9 +257,9 @@ void SharedFormulaUtil::unshareFormulaCell(const CellStoreType::position_type& a
         {
             // Move the top cell to the next formula cell down.
             ScFormulaCell& rNext = *sc::formula_block::at(*it->data, aPos.second+1);
-            --xGroup->mnLength;
             xGroup->mpTopCell = &rNext;
         }
+        --xGroup->mnLength;
     }
     else if (rCell.aPos.Row() == rCell.GetSharedTopRow() + rCell.GetSharedLength() - 1)
     {