Home

Excel Spilling Formulas and Can't Delete and No Array Found in Edit-Go Special - Going Crazy

%3CLINGO-SUB%20id%3D%22lingo-sub-375691%22%20slang%3D%22en-US%22%3EExcel%20Spilling%20Formulas%20and%20Can't%20Delete%20and%20No%20Array%20Found%20in%20Edit-Go%20Special%20-%20Going%20Crazy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-375691%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20problem%3A%26nbsp%3B%20Per%20the%20screenshot%20below%2C%20I%20seem%20to%20have%20an%20array%20that%20I%20can't%20locate%20and%20don't%20recall%20creating.%26nbsp%3B%20I%20can't%20seem%20to%20delete%20and%20cutting%20columns%20or%20inserting%20columns%20doesn't%20fix.%26nbsp%3B%20Frustrating.%26nbsp%3B%20If%20I%20%22cosmetically%22%20attempt%20to%20fix%20by%20having%20K46%20%3DK45%20I%20get%20a%20Spill%20Error%20in%20J46.%26nbsp%3B%20Error%20only%20happens%20when%20I%20put%20the%20ISTEXT(E46%3AI46)%20formula%20together.%26nbsp%3B%20How%20do%20I%20resolve%3F%26nbsp%3B%20Driving%20me%20nuts.%26nbsp%3B%20Chris%20Hunter%2C%20Farmington%2C%20NM%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F93428iB72D34314269711E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20Array%20Illustration.jpg%22%20title%3D%22Excel%20Array%20Illustration.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-375691%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-376176%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Spilling%20Formulas%20and%20Can't%20Delete%20and%20No%20Array%20Found%20in%20Edit-Go%20Special%20-%20Going%20Crazy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BThank%20you%20so%20much!%26nbsp%3B%20Couldn't%20find%20documentation%20on%20this%20issue%20anywhere%20and%20really%20appreciate%20your%20timely%20accurate%20response.%26nbsp%3B%20Formula%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%20Hunter%3CBR%20%2F%3EFarmington%2C%20NM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-375808%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Spilling%20Formulas%20and%20Can't%20Delete%20and%20No%20Array%20Found%20in%20Edit-Go%20Special%20-%20Going%20Crazy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-375808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302027%22%20target%3D%22_blank%22%3E%40Chris_Hunter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20appear%20to%20have%20tested%20the%20contents%20of%205%20cells%20so%20you%20should%20expect%20to%20get%205%20answers%2C%20each%20comprising%20the%20message%20or%20the%20sum.%26nbsp%3B%20This%20would%20be%20true%20for%20either%20a%20CSE%20array%20or%20a%20spilt%20dynamic%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20get%20a%20single%20result%20combine%20the%20Boolean%20array%20using%20OR%3C%2FP%3E%3CP%3E%3D%20IF(%20OR(%20ISTEXT(array)%20)%2C%20%22Error%20message%22%2C%20SUM(array)%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Chris_Hunter
New Contributor

My problem:  Per the screenshot below, I seem to have an array that I can't locate and don't recall creating.  I can't seem to delete and cutting columns or inserting columns doesn't fix.  Frustrating.  If I "cosmetically" attempt to fix by having K46 =K45 I get a Spill Error in J46.  Error only happens when I put the ISTEXT(E46:I46) formula together.  How do I resolve?  Driving me nuts.  Chris Hunter, Farmington, NM

Excel Array Illustration.jpg

2 Replies

@Chris_Hunter 

You appear to have tested the contents of 5 cells so you should expect to get 5 answers, each comprising the message or the sum.  This would be true for either a CSE array or a spilt dynamic array.

 

To get a single result combine the Boolean array using OR

= IF( OR( ISTEXT(array) ), "Error message", SUM(array) )

@Peter Bartholomew   Thank you so much!  Couldn't find documentation on this issue anywhere and really appreciate your timely accurate response.  Formula works.

 

Chris Hunter
Farmington, NM