cannot change "part of array"?

[jmckeone]

Went to change a formula in a worksheet I maintain and it gives me the error saying I cannot change part of an array. What on earth is that supposed to mean?

 

[Richard Schollar]

Hi

You've array entered an aray formula whilst more than one cell was selected - thus you have your array that you can't modify only a part of. To get around this, you need to select all the cells that are part of this array, F2 to edit and then confirm with Ctrl+Enter. This will convert them all to normal cells and you can edit as you wish.

 

[jmckeone]

Thanks for the tip.

 

[jmckeone]

That works fine on a single cell. I occasionally want to copy or move a range of cells containing array formulas and it is not practical to go cell by cell to turn formulas to normal ones for the purpose of the copy. Is there any technique for turning off array formulas during a copy and turning them back on afterwards?

 

[RoryA]

Copying shouldn't be a problem and you can move them if you move them all. I don't really see how it makes sense to move part of an array.

 

[jmckeone]

I don't really see how it makes sense to move part of an array.

That's the goofy part about the error, I'm not moving an array but rather moving cells containing array formulas. Probably more complicated to explain the interconnection it is being done for than it is worth at this point. Manually copied cells one at a time. Fortunately this type of modification is a fairly infrequent occurrence.

 

[RoryA]

But if one formula is array-entered into multiple cells, those cells form an array, so you are trying to move part of an array presumably.