Can't Change an Array

  • Thread starter Thread starter lj
  • Start date Start date
L

lj

I ocasionally run into a problem where I create array formulas within
excel. Later when I go back to edit them I get error messgaes that
say "can't change an array" and I can't make any edits, or delete
what's in the cells or even delete the cells. This is so
frustrating. What can I do so that I can edit these cells?

Thanks.

Lisa
 
You have to select the *entire* array of cells then edit the formula. For
example, the array block is A1:A5. Select the entire block A1:A5 then edit
the formula and re-enter as an array.
 
And just to add to Biff's note...

You can select the current array manually or you can let excel help:

select a cell in the range that holds the array
Edit|Goto (or F5 or ctrl-g)
Click Special
and click Current array

Or
Select a cell in that range
hit ctrl-/
(control slash is the shortcut key)
 
Most people, as a habit, revise a formula and then automatically hit
<Enter>.

To change an array formula, select any one cell containing the formula,
make your changes,
and then use CSE, <Ctrl> <Shift> <Enter> right off the bat.

This will revise the entire array range.

To delete formulas, you'll have to select the entire range, so follow Dave's
suggestion for a "bulk" selection prior to deletion.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

I ocasionally run into a problem where I create array formulas within
excel. Later when I go back to edit them I get error messgaes that
say "can't change an array" and I can't make any edits, or delete
what's in the cells or even delete the cells. This is so
frustrating. What can I do so that I can edit these cells?

Thanks.

Lisa
 
Back
Top