Have you used arrays in Excel?

[Wroey]

Have you used arrays in Excel?

I have inherited a couple of worksheets. The contents of
one sheet seems to be being pulled into another, not by a
specific, cell by cell linking, but by an array formula in
the destination sheet which specifies the entire range in
the target sheet.

Now I want to edit the range as I have enlarged the target
sheet by a few columns which were outside the range but
the formula, though amended via an edit and a ctrl-shift-
enter, doesn't do the job.

Any clues?

EG: {=+'P:\Finance\CSD_data\Block 2\Trading Plan\[June
2003 V3a ct play.xls]Trading Plan'!$A$1:$Q$252}

 

[Dave Peterson]

Try selecting one cell in that array. Copy the formula to the clipboard using
the formula bar. (hit escape)

Now with that one cell selected, hit Edit|Goto|Special|Current array.

You can enlarge that area and hit ctrl-shift-enter and it should work.
But if you want to reduce that area:
hit the delete key to erase the existing formulas.
Select your new range and paste that saved formula in,
adjust the formula to what you need
hit ctrl-shift-enter.

(I sometimes just delete the formula and paste in--no matter what's happening to
the range (just for my sanity's sake.)

Have you used arrays in Excel?

I have inherited a couple of worksheets. The contents of
one sheet seems to be being pulled into another, not by a
specific, cell by cell linking, but by an array formula in
the destination sheet which specifies the entire range in
the target sheet.

Now I want to edit the range as I have enlarged the target
sheet by a few columns which were outside the range but
the formula, though amended via an edit and a ctrl-shift-
enter, doesn't do the job.

Any clues?

EG: {=+'P:\Finance\CSD_data\Block 2\Trading Plan\[June
2003 V3a ct play.xls]Trading Plan'!$A$1:$Q$252}