Trying to Sort - Can't change part of an array error

%3CLINGO-SUB%20id%3D%22lingo-sub-1761587%22%20slang%3D%22en-US%22%3ETrying%20to%20Sort%20-%20Can't%20change%20part%20of%20an%20array%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1761587%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20linked%20some%20data%20with%20sumif%20formulas.%26nbsp%3B%20Now%20I%20want%20to%20sort%20the%20rows%20by%20job%20number%20and%20the%20color%20of%20backfill%20and%20I%20am%20receiving%20an%20error%20that%20states%20%22You%20can't%20change%20part%20of%20an%20array.%22%26nbsp%3B%20Is%20there%20a%20work%20around%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3EJanet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1761587%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1762508%22%20slang%3D%22de-DE%22%3ESubject%3A%20Trying%20to%20Sort%20-%20Can't%20change%20part%20of%20an%20array%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1762508%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F825452%22%20target%3D%22_blank%22%3E%40Janet_M5670%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EClick%20a%20cell%20in%20the%20matrix.%3C%2FSPAN%3E%20%3CSPAN%3EPress%20the%20F5%20key.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3ESelect%20%22Contents%22%20-%20%22Current%20Array%22.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ENow%20you%20can%20either%20select%20%22Cut%22%20in%20the%20marked%20area%20with%20the%20right%20mouse%20button.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EOr%20%22Copy%22%20-%20%22Paste%20Special%22%20-%20%22Values%22.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThen%20you%20only%20have%20the%20constants%20in%20that%20area.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EFormulas%20should%20be%20insured%20beforehand%20and%20then%20copied%20back%20again.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CBR%20%2F%3E%3CSPAN%3EI%20hope%20I%20understood%20the%20question%20correctly.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E*%20It%20is%20always%20helpful%20if%20you%20always%20indicate%20the%20operating%20system%20and%20Excel%20version%20of%20your%20topic%2C%20so%20that%20you%20come%20to%20a%20faster%20and%20more%20precise%20solution%20proposal.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1764107%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20Sort%20-%20Can't%20change%20part%20of%20an%20array%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1764107%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F825452%22%20target%3D%22_blank%22%3E%40Janet_M5670%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJanet%2C%20you%20can't%20apply%20SORT%20or%20modify%20somehow%20else%20part%20of%20the%20spill%20array%2Frange%2C%20you%20need%20to%20modify%20entire%20formula.%20That's%20hard%20to%20say%20anything%20more%20concrete%20without%20the%20sample%20of%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi, 

I linked some data with sumif formulas.  Now I want to sort the rows by job number and the color of backfill and I am receiving an error that states "You can't change part of an array."  Is there a work around?  

Thanks.

Janet

 

2 Replies

@Janet_M5670 

 

Click a cell in the matrix. Press the F5 key. Select "Contents" - "Current Array".
Now you can either select "Cut" in the marked area with the right mouse button.
Or "Copy" - "Paste Special" - "Values".
Then you only have the constants in that area.

Formulas should of course be insured beforehand and then copied back again.


I hope I understood the question correctly.

 

* It is always helpful if you always indicate the operating system and Excel version of your topic, so that you come to a faster and more precise solution proposal.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

@Janet_M5670 

Janet, you can't apply SORT or modify somehow else part of the spill array/range, you need to modify entire formula. That's hard to say anything more concrete without the sample of the formula.