Writing over Spilled Legacy Array Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2346309%22%20slang%3D%22en-US%22%3EWriting%20over%20Spilled%20Legacy%20Array%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346309%22%20slang%3D%22en-US%22%3E%3CP%3EI%20always%20try%20to%20learn%20from%20Microsoft%20Templates.%20I%26nbsp%3B%20came%20across%20this%20Photo%20calendar%20template%20were%20the%20author%20was%20able%20to%20make%20cells%20write%20over%20legacy%20spilled%20arrays%20with%20no%20errors.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Cell.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279991i0AAF9BB2553E71CA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Cell.png%22%20alt%3D%22Cell.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDoes%20anyone%20know%20how%20to%20duplicate%20this%3F%3C%2FSTRONG%3E%20I%20thought%20we%20are%20unable%20to%20write%20in%20cells%20that%20occupy%20an%20array%20formula.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2346309%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346852%22%20slang%3D%22en-US%22%3ERe%3A%20Writing%20over%20Spilled%20Legacy%20Array%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346852%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1051216%22%20target%3D%22_blank%22%3E%40DobroSKILLS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20on%20Excel%20with%20dynamic%20arrays%20you%20enter%20formula%20without%20CSE%20it%20returns%20spill%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20987px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280106i6F96CE45C27BDC04%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EHowever%2C%20if%20you%20enter%20same%20formula%20with%20CSE%20it%20returns%20only%20first%20element%20of%20the%20array%2C%20that%20how%20dynamic%20array%20formula%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20867px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280107iB5F1D3B068CF03C8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20may%20try%20on%20any%20other%20formula%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20232px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280108i2BFFEE08AB136921%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I always try to learn from Microsoft Templates. I  came across this Photo calendar template were the author was able to make cells write over legacy spilled arrays with no errors. 

Cell.png

 

Does anyone know how to duplicate this? I thought we are unable to write in cells that occupy an array formula. 

1 Reply

@DobroSKILLS 

If on Excel with dynamic arrays you enter formula without CSE it returns spill

image.png

However, if you enter same formula with CSE it returns only first element of the array, that how dynamic array formula works

image.png

You may try on any other formula

image.png