Can't change part of an array

[Geoff Newham]

Hi,
This has been particulary galling and it's not the first time, either!
I had this message: "Can't change part of an array". It popped up while
dealing with some corrections to a sheet. I was left with no option but to
ctrl+alt+delete to end the task since I could not click on any part of the
sheet or other worksheets nor could I even delete the sheet or worksbook and
most functions are greyed out.

The formula was ={SUM(IF(Purchases!$F$2:$F$5000="Goldman
Sachs",IF(Purchases!$D$2:$D$5000>=DATEVALUE("29/04/2004"),Purchases!$I$2:$I$5000,0),0))}.

Since I discovered that this actually replicated the cell above and should
have been analysing purchases via Investec, not Goldman Sachs, I changed the
formula to read Investec. This is one of 25 different brokers in a list where
each one analyses the purchases using the above formula.
The system baulked at the first change and so I deleted the whole formula to
re-write it, then the notice popped up.
Q1: If this notice pops up again, is there any way to back out of it
without losing everything?
Q2: What causes this notice to pop up? If I understand that, I can avoid
the cause.

Many thanks
Geoff

 

[Jacob Skaria]

If you have selected a range and array entered...To edit or remove you will
need to select the same range and edit the formula in active cell and array
enter again(Ctrl+Enter)

This is not an answer to your query. You can rewrite your formula as a
non-array one

=SUMPRODUCT((Purchases!$F$2:$F$5000="Goldman
Sachs")*(Purchases!$D$2:$D$5000>=DATE(2004,4,29)),Purchases!$I$2:$I$5000)

If this post helps click Yes

 

[Geoff Newham]

Jacob,
Thanks for the fast response.
I have to admit to not understanding your explanation of what I need to do.
I did copy and paste the suggested, changed formula and, what happened?
The cell I was trying to change simply reverted to the previous formula -
that is, it reverted to a replication of the cell above!

 

[Sean Timmons]

OK, so you want to look at a different investment firm.. Just change the name
from Goldman Sachs to Investec in the below SUMPRODUCT formula.

The formula will look on the purchases tab of your workbook for the name
"Investec", then verify the date in column D for those rows is at least April
4, 2009 and return the sum of your column I values in the matching rows.

 

[Geoff Newham]

Thanks Sean. But, yes, I did all that and it still returned the Goldman stats.
I have since sorted it by working on a different cell and replicating the
amended formula back across the 'traumatised' cell and it worked.
It would be good to know how to prevent this pop up message recurring by
knowing what to avoid doing in future. Our in-house Actuary couldn't figure
it out, either!

 

[Sean Timmons]

If you delete all your SUM formulas and go with SUMPRODUCT, you won't receive
thsi error in the future...

Presumably, your SUM formula spanned multiple cells, and it appears you were
attempting to make a change to one cell first, which most likely caused the
error.

Can always just delete all SUM cells except the current one, then make the
change.