David Gurney wrote:Dear Barry,
Many thanks for your reply to my question. I have been trying to use the format you suggest and to start with I find that I can enter it into three columns of the third Spreadsheet without any difficulty. However after that the formula simply will not enter correctly. I will go through the steps that I have tried to no avail and wonder if there is a bug somewhere in the software.
Possibly, but I'm not seeing any problems with my (now expanded) test files.
3. Type in the formula as follows:- =IF("BS 1.cwk"!I2="BS 2.cwk"!B2,"BS 1.cwk"!I2," ")
This formula presents a slightly different situation that your initial example. In the first example the cell references were to the same cell on two different tables, and the formula was placed into the same cell on the third table.
Here, the formula in column H references cells in column I of the first table and column B of the second. That in itself is not a problem, but could, if Filled right, result is an Bad Cell error.
"Bad Cell" usually means that the formula is referencing a cell that does not exist. If you fill this formula down to row 15 on table 3, and either of tables 1 and 2 has only 14 rows, then all references in the formula in the bottom row of table 3 will refer to B15 or I15 of the other two tables—cells in a row that at least one of the tables does not have, or in the error message, a "Bad Cell".
as mentioned, there doesn't appear to be any reason for the formula to not work in more than three columns. Here's the expanded example I referred to above.
I increased the size of each spreadsheet to 17 columns by 4 rows.
The first contains only text entries, entered into column A, then filled right into the rest of the columns.
The second contains the same entries in the same order in column A. All other cells contain the formula:
=CHOOSE(RAND(4),"Yellow","Red","Blue","Orange")
which make a random choice to determine which of the four values will be placed into its cell.
The third table contains only the formula
=IF("external 1.cwk"!A1="external 2.cwk"!A1,"external 1.cwk"!A1,"no")
filled right and down to every cell.
The three cell references all change to match the cell they are in.
Response in table 3 to a recalculation of table 2 is essentially instantaneous, and the speed does not change when Table 1 is closed.
The prime suspect whenever AppleWorks slows down is an over-full Recent Items folder. Quit AW, then in the finder, go to your-name > Documents > AppleWorks User Documents > Starting Points > Recent Items. Delete all or almost all of the items in that folder. Relaunch AW, and you should notice some speed improvements.
For other misbehaviours, the usual suspects are the preferences files.
There are several of these, all found at your-name > Library > Preferences.
Quit AppleWorks, then in the Finder, follow the path above.
In the Preferences folder, locate and delete the file com.apple.appleworks.plist.
Still in Preferences, locate and open the folder AppleWorks.
In that folder select and delete the two cache files and the AppleWorks 6 Preferences file. If you have made changes to the button bar, but have no issues that seem Button bar related, ignore the Button Bar file.
Relaunch AppleWorks.
Regards,
Barry