Copy and drag array formulas

[Mike Baker]

I have a very large spreadsheet that has a number of columns containing array formulas. I am extending the sheet by introducing more columns to the right of the existing spreadsheet. The problem is that when dragging the formulas down they will only copy so far and no further. I've checked the content of the empty cells and they are not locked. I've tried both automatic and manual saves to no avail. Any ideas will be gratefully received. Incidentally, I am running Excel 2000.

Thanks!

 

[Andrew Poulsom]

Do you get some sort of message from Excel? If so what is it?

 

[Mike Baker]

No - there is no message displayed I'm afraid. Could it be a virtual memory problem on my PC by any chance?

 

[Andrew Poulsom]

What's the formula and where is it? Where are you trying to copy it?

 

[Mike Baker]

The formula is {=IF(ISERROR(INDEX('Data Download Area'!$B$1:$K$700,SMALL(IF('Data Download Area'!$D$1:$D$700=A11,ROW('Data Download Area'!$D$1:$D$700)),ROW($4:$4)),1)),"",(INDEX('Data Download Area'!$B$1:$K$700,SMALL(IF('Data Download Area'!$D$1:$D$700=A11,ROW('Data Download Area'!$D$1:$D$700)),ROW($4:$4)),1)))}

This formula exists quite happily in several thousand cells already and works correctly in 8 columns. If I try to copy it to new columns then it will only copy so far down, (2915 rows in one case and 315 rows in another.

 

[Andrew Poulsom]

In what cell is that formula?

 

[Mike Baker]

It and a very similar version is in many cells but for example it's in cells Z2 to Z342 inclusive and then will not copy down to any cells Z343 and beyond. In another example its in cells V2 to V2915 and then will not copy down any further.

 

[Andrew Poulsom]

I don't have Excel 2000 to test, but in Excel 2003 if I put that formula in Z2 I can copy it down to at least row 5000. So I think something strange is going on in your workbook. Can you copy that formula in Z2 and paste it into Z5000?

 

[Mike Baker]

I tired that but to no avail. The format is copied but not the formula. I have also increased the virtual memory on my PC but that has not helped either. It's very strange .....

 

[Andrew Poulsom]

Is the worksheet protected? Is the cell Hidden?