8 replies to this topic

[K e n]

In a number of worksheets.I am using a Name, AverageOfThisRowUpToColumnPreviousToThisColumn. The Name refers to the following formula: =FIXED(SUM(INDIRECT(ADDRESS(ROW();2)):INDIRECT(ADDRESS(ROW();COLUMN()-1)))/COLUMN()-2;2), which gives the average of cell contents in a row from column B up to the column before the one containing the Name. Works OK, BUT... Why does Excel crash whenever I try to alter the formula in a cell already containing this Name, or whenever I try to introduce the Name into a new cell? About the only thing I can do without provoking an immediate collapse of Excel is to copy succesful cells containing the Name across (or down). I don't have this problem with other Names. --- Ken

[appleoddity]

My suggestion would be to upgrade to a newer version of Office, make sure you have installed all your Microsoft updates for Office, and/or reinstall Office.

[K e n]

My suggestion would be to upgrade to a newer version of Office, make sure you have installed all your Microsoft updates for Office, and/or reinstall Office.

[K e n]

Thanks for your attention, appleoddity.

I wrote the formula within Office 2007 and would feel very much cheated if I had to acquire newer software in order for it to function properly. I have only just reinstalled Office and Windows7 after a major crash. The problem I describe occurred both before and after the reinstallation. What really puzzles me is the fact that the construct works (given an additional pair of brackets around "COLUMN() - 2", which I inadvertantly left out in my original posting). It just refuses to let me alter (improve?!) it.

K e n

[appleoddity]

Hi Ken. Have you installed all your Microsoft Office service packs and updates? Office 2007 should be at service pack 3. In Windows 7, you must choose to check for updates for "windows AND other products" in order to receive Office updates. You find that setting by choosing Windows updates from your start menu under programs. Its possible this is a known issue and has been fixed with updates. Other than that, I am not an Excel expert - sorry. But, my own personal solution would be to try to delete the formula and re-create it from scratch. Or, scrap the file and start over from fresh, trying to copy and paste the information that you can. I just don't have any other suggestions for you.

[K e n]

Hi, Appleoddity, I have just checked my update history; One entry was "The 2007 Microsoft Office Suite Service Pack (SP3)" . Tthe status was "Successful" and the Date Installed "29.12.2011". So if there's nothing wrong with the formula then this must be another Excel bug. I'll try your other suggestions to see if that makes a difference; but that still would not explain why the fault keeps happening- and that is what is bugging me most of all. Thanks again, K e n

[paws]

Hi, Have you tried opening your Excel file using different computers...running Win XP, Vista, 7 etc and see if the problems persists? Try different versions of Excel...2007 and 2010 and as a standby also try a machine with Excel 2003 (with the file format converter included) If all the machines running all versions of Windows and all versions of Excel all have the same identical problem...you will at least know where you stand. Regards paws

[K e n]

Hallo, Paws. Sorry to be so slow to react (just got back after hospitalisation for brocken fingers). Thanks for your suggestions: however, I'm afraid my knowledge of the software involved is insufficient to allow me to interpret the results of the tests you mention: I would, in fact, not "know where I stand". I have an old PC with XP and an older Office version. Asoon as I can use my injured hand again I'll drag it out and experiment. K e n

[paws]

Hi Ken, sorry to hear about the fingers...I sure hope they mend soon... Yes when you are ableto.... try to use the alternatives I suggested.... There's no interpretation needed... it'll either work or it wont! Regards paws