Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 Feb 24 '11 at 19:50
    
The values there are generated by the perl script I mentioned, and are simple numbers; not the result of other formulas, etc. –  Glen Solsberry Feb 24 '11 at 20:31
    
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 Feb 24 '11 at 22:48
    
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 Feb 25 '11 at 0:24
1  
also, is this actually an xls file or some variety of delimited file ? –  datatoo Feb 25 '11 at 0:35

5 Answers 5

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))
share|improve this answer
    
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 Feb 28 '11 at 21:24

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)"
share|improve this answer

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

share|improve this answer
    
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 Feb 25 '11 at 13:28
    
@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 Feb 25 '11 at 18:43

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.

share|improve this answer

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.

share|improve this answer
    
Unfortunately, the problem isn't with the calculation mode, as this is already set. –  Glen Solsberry Feb 24 '11 at 14:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.