Insert Rows into an existing array

[Guest]

I have a spreadsheet with a series of formulas in various columns. I now want
to insert more rows that will follow the formula rules of the existing
columns. How do I overcome the message "you cannot change part of an array"?

Thanks in advance

 

[Bernie Deitrick]

Pav,

Go to the cells that have the array entered formula, select them, enter edit
mode (when the first cell is the active cell), press home, and insert a
single quote, then press Ctrl-Enter (not Ctrl0Shift-Enter). Do this for all
multi-cell array formulas. Then insert your rows, then reselect the cells
(plus extra for the newly inserted rows) then enter edit mode, press home,
delete the single quote, and press Ctrl-Shift-Enter.

When you use multi-cell array formulas, you need to be aware of that
limitation, and design your sheets accordingly.

HTH,
Bernie
MS Excel MVP