'You can't change part of an array' When Sorting or Editing/Deleting Cell in Excel

In this Microsoft Excel video tutorial I demonstrate how to deal with the 'You can't change part or an array' error message. You may encounter this error message when you are attempting to sort data or when you are trying to edit or delete a cell.

Video contents:

00:00 Introduction
00:16 You get the error when sorting data
01:48 You get the error when editing or deleting a cell

[IvanCortinas_ES]

Yeah. Great tutorial. Very well explained the subject of array formulas. Thank you Chester.

[peasem]

thank you for showing how to fix this, it worked immediately!

[danielp.]

Huge. Damn unique thing, knew that was it right when i opened this video

[ethanhusted]

@chester tugwell, I am having trouble figuring out a formula for a index and match, one tab has a name in column a that goes down 100 lines, and in column B can have single numbers 1-40 or can be inputed as a range like 5-10 column b is in text format. on the second tab i have sequence(40, , 1, 1) how can i return a name in column b on the second tab if the number is = or between the first and last diget. I cant figure this out. if anyone can help that would be great
sorry so long

[patrickschardt]

Luckily the curly bracket array issues are solved by the new dynamic arrays

And SORT solves it for the dynamic arrays. LET, HSTSCK, VSTSCK eliminate the need for multiple columns

[dannaibnsaid]

how to design a inventory for multiple shop in excel