2
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.
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.090since 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