Sort chart which contains an array--I'm hung up!

[Terry B.]

I'm essentially a newcomer to Excel, so this question ought to be
comically-simple for the veterans out there: I've got a very simple
chart, 12 columns and 42 rows, the first column is dates, the
remaining columns are all numeric. One of the numeric columns was
generated by running an array formula, and the calculation went
fine...My problem is, when I try to sort the table, using Column A
[the Date column], I get an error message "You cannot change part of
an array." I've tried saving the chart, retrieving it and retrying,
have been through the books I've got and have spent a couple of hours
on this...CAN ANYONE HELP?
Thanks for your time and attention to detail ;>

Terry

 

[Peo Sjoblom]

Is the array alive, that is will the values change. If not copy it and paste
special as values
in place. If not maybe you could post the formula, maybe there is a
workaround..

 

[A.W.J. Ales]

Terry,

The problem is exact what the error message says : You are trying to change
( bij the sort) the arrayformula.
Consider the arrayformula as "one entity". By the sort you are trying to
change that entity.

The easiest way around is probably to calculate the array formula cells
somewhere else on your sheet and copying the cell value into the area where
you are building your chart. (this copying can be done with the normal
cellreferences to the calulated cells).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Terry B.]

Terry,

The problem is exact what the error message says : You are trying to change
( bij the sort) the arrayformula.
Consider the arrayformula as "one entity". By the sort you are trying to
change that entity.

The easiest way around is probably to calculate the array formula cells
somewhere else on your sheet and copying the cell value into the area where
you are building your chart. (this copying can be done with the normal
cellreferences to the calulated cells).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

I'm essentially a newcomer to Excel, so this question ought to be
comically-simple for the veterans out there: I've got a very simple
chart, 12 columns and 42 rows, the first column is dates, the
remaining columns are all numeric. One of the numeric columns was
generated by running an array formula, and the calculation went
fine...My problem is, when I try to sort the table, using Column A
[the Date column], I get an error message "You cannot change part of
an array." I've tried saving the chart, retrieving it and retrying,
have been through the books I've got and have spent a couple of hours
on this...CAN ANYONE HELP?
Thanks for your time and attention to detail ;>

Terry

THANKS TO BOTH OF YOU for the quick and helpful posts to my question.
Your suggestions gave me the intuition and focus I needed to go back
into Excel's Help contents and find what I was needing: the section on
'replacing a formula with its results.' I did, and it works great :-}
Have an Excellent Day!
Terry.