Excel not properly recalculating values

I have an Excel sheet with values in it (this sheet is generated by a custom perl script, but I don't think that's where the problem lies). In it, I have a formula:

=sum(indirect(concatenate(address(6,column()),":",address(17,column()))))

The purpose of this formula is to give me the SUM() of the cells in the current column, between rows 6 and 17. In Gnumeric Spreadsheet, as soon as I open the file, this works. But in Excel (both 2003 and 2007), opening the file gives #VALUE! errors in the fields with this formula, stating that the INDIRECT call with the values $B$6:$B$17 will result in an error.

Here's the kink in the issue. If I edit the field (via F2), and make no changes, and hit enter, the values update. Also, it seems, if I save the file as .xlsx (Excel 2007 format), the values update upon opening. Unfortunately, I'm not sure that creating an xlsx is a possibility with the modules that I'm using, and many of our clients probably wouldn't be able to use it anyway.

Any suggestions? Editing 200+ files every month for each client isn't going to be feasible, so if there's something I'm missing, please let me know.

Glen Solsberry

Posted 2011-02-03T14:45:05.223

Reputation: 363

How are the numbers getting into $B$6:$B$17? Pasting, a data refresh, external link, formulas? Perhaps no values are initially available on the file open. – datatoo – 2011-02-24T19:50:32.007

The values there are generated by the perl script I mentioned, and are simple numbers; not the result of other formulas, etc. – Glen Solsberry – 2011-02-24T20:31:09.973

does this happen only for this formula or also for others? are you able to reproduce it in documents not built with the perl library? As formulas are stored in a complex format (not as text) in .xls files, to automatically translate function names for non-English Excel versions, maybe there is a bug in your excel lib that creates a formula that "renders" the same but "acts" differently? – mihi – 2011-02-24T22:48:53.943

have you tried the trace error button on the formula auditing toolbar to see if it highlights a source cell. It seems like this may be misinterpreted as an array formula by excel. does this come out the same? =SUM(INDIRECT(ADDRESS(6,COLUMN(),1,1)&":"&ADDRESS(17,COLUMN(),1,1))) – datatoo – 2011-02-25T00:24:50.403

1also, is this actually an xls file or some variety of delimited file ? – datatoo – 2011-02-25T00:35:24.353

Could you upload an minimal example of one of the broken .xls files? That way one could play around with the file to find the error. – Darokthar – 2011-02-25T00:44:57.087

Is the formula that errors in a row from 6 to 17? If it is this would create a circular reference. Also, and just curious here, why are you using such a convoluted formula when =SUM(B$6:B$17) (for column B) would do the job? – chris neilsen – 2011-02-25T08:09:38.550

@milhi This happens for "all" formulas in the sheet, though all of them resemble this in some minor modification. – Glen Solsberry – 2011-02-25T13:30:05.533

@datatoo I have; the problem seems to stem from the INDIRECT call (as mentioned in the original question) with values $B$6:$B$17, stating that it will give a #VALUE! result. Also, this is an actual .xls file. – Glen Solsberry – 2011-02-25T13:31:29.650

@Darkthar I'll see what I can do. – Glen Solsberry – 2011-02-25T13:32:04.890

@chris neilsen They are not. The formula is in B19, so no circular references are happening. As to why the convoluted formula, the perl module I'm using doesn't give me "easy" access to the character for the current column, so I had to do some trickery to determine it. – Glen Solsberry – 2011-02-25T13:33:17.090

since it seems you are entering this in multiple columns for results on each, it would require a calculation of each to determine the range in each instance. It may mean forcing calculation on the sheet before sending to the users since the same formula provides different results depending upon its contextual column. – datatoo – 2011-02-25T15:45:55.770

@gms8994 could you show the code for the line of perl that inserts your current formula? and what row do you typically prefer this be written to? – datatoo – 2011-02-26T01:13:02.447

@gms8994 The perl script may indeed have a problem with this. What are you using? – datatoo – 2011-02-26T04:21:55.647

Answers

If you insert a vanilla Sum function does it work - i.e. can the problem be traced to a component of the indirect / address combination?

Have you tried using OFFSET to create the range to be summed? For example:

=SUM(OFFSET(B6,0,0,12,1))

Diem

Posted 2011-02-03T14:45:05.223

Reputation: 96

Tend to agree with this one. INDIRECT is a bit tricky to get right and has caused other problems (search for indirect and excel on superuser) – Rhys Gibson – 2011-02-28T21:24:47.460

Might be clutching at straws, but you could try R1C1 style reference

=SUM(INDIRECT("R["&7-ROW()&"]C:R["&17-ROW()&"]C",FALSE))

Or if the formulas are always in row 19, set the reference style to R1C1

Application.ReferenceStyle = xlR1C1

Then you can insert the formula as

=SUM(R[-13]C:R[-2]C)

Update:

you can avoid the need to set ReferenceStyle by using

<YourCellReference>.FormulaR1C1 = "=SUM(R[-13]C:R[-2]C)"

chris neilsen

Posted 2011-02-03T14:45:05.223

Reputation: 4 005

Maybe you could force a recalculation of the whole spreadsheet with this:

http://msdn.microsoft.com/en-us/library/bb687891.aspx

A macro renaming and then again renaming the Spreadsheet should work fine. Maybe you could execute in at the end of the pearlscript? I really don't know too much about pearl sorry.

Or use the hotkey CTRL+ALT+F9 and if it works use VBA with this function: Application.CalculateFull

Darokthar

Posted 2011-02-03T14:45:05.223

Reputation: 1 361

The idea is to fix the problem before opening the sheet; and I doubt I'm even able to attach VBA to the file anyway... – Glen Solsberry – 2011-02-25T13:28:48.717

@gms8994 If the hotkey above works, you could run a VBA-Script with this as basis: stackoverflow.com/questions/2050505/… which updates all the files. Afterwards you could deliver the files. You could start the script at the end of your pearl script. – Darokthar – 2011-02-25T18:43:18.597

If you are using WriteExcel for your perl scripting, note this, with a suggestion in the article. parsing INDIRECT & CONCATENATE functions and Formula error mixing SUM() and INDIRECT()

Unfortunately the Spreadsheet::WriteExcel parser cannot handle formulas like that. Spreadsheet::WriteExcelXML can handle them though if you can use that.

datatoo

Posted 2011-02-03T14:45:05.223

Reputation: 3 162

Under Excel's Tools | Calculations you can set the calculation mode. If it set to 'Automatic' Excel should update all fields' formula results automatically, also when opening the file.

Calculation setting can also be set per individual worksheets, so maybe it's possible that the Perl script sets manual update mode.

Jawa

Posted 2011-02-03T14:45:05.223

Reputation: 3 349

Unfortunately, the problem isn't with the calculation mode, as this is already set. – Glen Solsberry – 2011-02-24T14:06:43.383