Excel 2007 : Excel - Locking up / Hanging

[themaster25]

An excel spreadsheet I am creating is causing issues. It has 270 rows with 4 columns for the main data on sheet 1. There are formulas, including indexes on Sheet 2 and 3 which also cross reference each other. When I attempt to copy/update or delete or paste in rows on Sheet1, Excel locks up and the Excel.exe process hangs with 100% cpu. I then need to close down and restart Excel. Has anyone had similar issues and can let me know of any solution?

[Richard Buttrey]

What's the file size?
If it appears to be large have you checked the obvious things like the last used row on a sheet?. Sometimes formulae can accidentally get copied down all 1m + rows. or across all 16k+ columns.

[themaster25]

What's the file size?
If it appears to be large have you checked the obvious things like the last used row on a sheet?. Sometimes formulae can accidentally get copied down all 1m + rows. or across all 16k+ columns.

It's only 367Kb

[Richard Buttrey]

Are there any macros running?

[themaster25]

Thanks. There are no macros, only formulas. One of the main formulas I have is:

=INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$D:$D=Sheet1!$E$2,IF(Sheet1!$B:$B="XXXXXXXXX",ROW(Sheet1!$A:$A))),ROW(Sheet1!1:1)),1)

This is entered as an array. Basically, this formula is on Sheet2 and is finding a particular value from Sheet 1 Col E, then which has a particular value in Sheet 1 Col B, and inserting the corresponding value from Sheet 1 Col A, into my table in Sheet2.

Because of what I am needing to achieve, this formula is contained within 12 tables of 300 rows each.

It is most likely what is killing my process. Is there an easier way to do what I am trying to achieve?

[FDibbins]

your problem may be with including entire columns in your index, i have done that and had the same results you described. try taking it down only as far as you need to, and see if that fixes the problem?

[Richard Buttrey]

I would concur with FDibbins.

Remember array formulae are exceedingly memory intensive since every cell in the range used has to be evaluated, and since you're effectively asking Excel to evaluate all 1m + rows it's not surprising that it's locking up.

So change all your 12 sets of formulae to

=INDEX(Sheet1!$A1:$D300,SMALL(IF(Sheet1!$D1:$D300=Sheet1!$E$2,IF(Sheet1!$B1:$B300="XXXXXXXXX",ROW(Sheet1!$A1:$A300)) ),ROW(Sheet1!1:1)),1)