SpreadCE / Spread32

Stephen....

Have -2- workbooks. Workbook 1 is for June data and workbook 2 is for July data.

In workbook2, the following formula is used to add in certain June data to the July data---
'=('c:\folder1\folder2\folder3\filename\[workbook1.xls]sheet1'!S35)

Data, in the referenced cell, shows up in workbook2 as it should. However, there are -8- columns that need to be summed so I figured it would be simpler to sum the -8- columns in a cell on workbook1 and then have the single formula in workbook2 vs. having to string 8 references in an =Sum(..) formula (which works by the way).

Used an adjacent column in workbook1 and did the sum formula (formatted as [h]:mm, same as in all -8- columns) Entered in workbook 2 the above formula, substituting the cell reference on workbook1 that had the sum of the 8 columns. Returns 0:00 (formatted as [h]:mm).
Changed the cell reference to some of the other cells in workbook1 that was part of the sum figure and it returned a correct value for those particular cells.

In workbook2, I have deleted the formula, formatted the particular cell to "General", saved, saved as, deleted contents, saved, saved as, closed Spread32, opened Spread32, reformatted to [h]:mm, typed the formula again as it should be---nothing works to get the cell total that I want in workbook1 to show up.

This isn't the first time its happened, but normally, I can go thru the above
sequence and then a formula will function. Have run out of ideas as to why it won't return the data from the needed cell in workbook1.

Any ideas??

ray

Hi Ray,

I think that this is what is happening:

Unlike Microsoft Excel, Spread32 does not currently lock the files that you open to prevent them from being accessed by other applications. One result of this is that you can open the same spreadsheet file in more than one instance of Spread32 at the same time.

When you have an external reference in a spreadsheet, such as
=[workbook.xls]Sheet1!S35
Spread32 opens that file in the background, in exactly the same way as if you had opened the file yourself in the same instance of Spread32, except that it keeps it hidden (i.e. not on the list of open files in the File>Window menu option). All further values that are required from the "external" workbook are then fetched from the hidden open file.

If you start a second instance of Spread32 and open the referenced file, any changes that you make there will be ignored by the first instance of Spread32 because it already has a copy of the file open.

If instead you open the external file in the first instance of Spread32, it will just change the already-loaded file from hidden to visible. Then you can make changes and they will be reflected in formulas that use external references.

If in doubt, don't be editing a file and the external references to it at the same time.

Hi.....

At the time, I had the June & July workbooks open at the same time so as I could flip back and forth to make sure the column/cell references were entered correctly. If I understood your reply, this should have worked.
Right ??

Just to see what would happen, I only opened the July workbook and made sure that the formula syntax was correct with respect to the path of the external workbook (June). When I hit ENTER, an error message pops up --
Unable to open input file (with the path to the external file shown on the line below). If I click the OK button, the message stays there. Only by quick
repetitive hits (7+) on the ENTER key can I get the error message to be gone. Then, the cell contents show-- #REF! . Saved & closed the workbook (July).

I then opened the June workbook, then opened the July workbook.
When the July workbook opened, it wanted to know if I wanted to update
the links -- clicked YES. Then the "Unable to open input file" error message popped up. The #REF! is still in the cell where the formula is
located.

The formula in this cell was copied/pasted from another workbook where it was functioning correctly. All that was needed was to change the .xls file name ,with respect to the external path portion of the formula, and the cell references. I went character by character to verify that file names were properly typed as well as cell ranges (although the latter would not affect its' ability to open the file; only incorrect data would be included).

Is there a limit on the length/ # characters in a formula OR # of external references in a formula ??? Any effect of copying/pasting formulas between workbooks (outside of changing file/cell references) ?? What about the renaming of a file ?? I renamed the current files I'm working with and have opened them under the renamed titles without any problems. Yes, I've edited the formula to reflect the current file name(s).
I've not had this problem before. There have been times when I've had as many as -5- different workbooks open and then copied and pasted between them.

Obviously, I missing something here but can't see it.

Ray