Linking 3 spreadsheets text using Appleworks 6.2.4

This forum is a self-help system for the exchange of ideas among AppleWorks users.

Linking 3 spreadsheets text using Appleworks 6.2.4

Postby David Gurney » Fri Jan 14, 2011 6:50 am

Is it possible to link two spreadsheets cells containing text so that if the text is identical in spreadsheets 1 and 2 it appears in a cell contained in spreadsheet 3?

e.g. Spreadsheet 1 Cell A1 contains the word "RED"
Spreadsheet 2 Cell A1 contains the word "RED"
Spreadsheet 3 The word "RED" is printed in cell A1

If the cells contained in Spreadsheets 1 and 2 do not match then nothing is printed in cell A1 of Spreadsheet 3.

Any advice will be much appreciated.

Regards,

David Gurney.
David Gurney
 
Posts: 18
Joined: Tue Feb 03, 2004 5:36 pm
Location: Scotland

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby Barry » Sun Jan 16, 2011 3:22 am

David Gurney wrote:Is it possible to link two spreadsheets cells containing text so that if the text is identical in spreadsheets 1 and 2 it appears in a cell contained in spreadsheet 3?

e.g. Spreadsheet 1 Cell A1 contains the word "RED"
Spreadsheet 2 Cell A1 contains the word "RED"
Spreadsheet 3 The word "RED" is printed in cell A1

If the cells contained in Spreadsheets 1 and 2 do not match then nothing is printed in cell A1 of Spreadsheet 3.


Good question, David!

It's been a while since I used a reference to an external spreadsheet in AppleWorks, so I had to take a look in the Help document. I found this under "Using spreadsheet cell references in formulas."

AW Help wrote:Including references to cells in other spreadsheet documents or frames

Note: A document or frame name must be enclosed in quotation marks if it contains spaces, special characters, or resembles a cell address or function name.

To enter a reference to a cell in another spreadsheet:
• Type the document name, an exclamation point (!), and the cell address.
For example:
• =Budget.CWK!A2
• =SUM("Planning Budget"!A2..A31)


I created three spreadsheets, "external 1.cwk", "external 2.cwk" and "external 3.cwk", and entered the word yellow in A1 of the first and second.

In the third, I entered this formula into cell A!:

=IF("external 1.cwk"!A1="external 2.cwk"!A1,"external 1.cwk"!A1,"no")

As I had created a single spreadsheet, Saved it with the first name, Saved as with the second name and again with the third name, only version 3 was open when I entered the formula and pressed return to confirm it.

Cell A1 immediately showed "Yellow".

I reopened external 1, and changed the entry to Green. There was no change to external 3 until I told it to Calculate Now, at which point the contents of A1 changed to "no".

Opening external 2 and changing the content of A1 to Green caused the content of external 3 cell A1 to Green, again after causing the spreadsheet to recalculate, this time by pressing shift-command-=.
The comparison is not case sensitive.

I used "no" in the IF statement to give a visual confirmation that the formula was working correctly. It's not possible to write a formula so that "nothing is printed in cell A1" under a specific circumstance. The closest we can come to that is to return a null string ( "" ) which will appear empty, although technically, it's not.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby David Gurney » Wed Jan 26, 2011 7:02 am

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.

1. Open the third Spreadsheet which is called "Comparison Spreadsheet".
2. Select the first cell under a column, say H2. Click the Entry box and then the "Insert Function" fx and select IF.
3. Type in the formula as follows:- =IF("BS 1.cwk"!I2="BS 2.cwk"!B2,"BS 1.cwk"!I2," ")
4. Next I press "ENTER". In the cases where there is no problem it takes a few seconds and then 0 is printed in H2. After this I select a number of Cells in the H column and use the "FILL DOWN" command to transfer the formula to the other Cells in the column that I wish to compare.
5. After doing this to three columns I find that it becomes impossible to enter the formula and get a correct result. One of three things occur:- (a) the "Spinning Ball" syndrome takes place and the spreadsheet freezes. The only way of getting out of this is to "Force Quit". Alternatively after a time the word #ARG appears in the first selected Cell and on reselecting it I find that the formula has either become truncated so that it is incorrect or a BADCELL is listed in the enter bar.

I have tried various combinations in an endeavor to get over this problem but so far without success. I wonder if the fact that I have two separate Appleworks on the computer, 6.2.4 and 6.2.9, is causing the difficulty.

Have you any suggestions that I can try so that it may be overcome?

I will look forward to hearing from you and would thank you for you assistance.

Kind regards,

David Gurney.
David Gurney
 
Posts: 18
Joined: Tue Feb 03, 2004 5:36 pm
Location: Scotland

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby Barry » Sat Jan 29, 2011 3:35 am

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.
Image

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
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby David Gurney » Sat Jan 29, 2011 10:30 am

Dear Barry,

Very many thanks for your advice which I really appreciate.

As suggested I have deleted the preference files and tried again with an empty spreadsheet. All went well for the first three columns - the formula was entered in the cells starting in the second row and returned a 0 answer in each of the appropriate cells. However this did not work on the following cell as I entered the formula as follows:-

IF("BS 1.cwk"!AP2="BS 2.cwk"!K2,"BS 1.cwk"!AP2," ")

and after pressing "ENTER" instead of a zero being returned in the relevant cell I got a #ARG! report.

On selecting the original cell I in which #ARG! had appeared I then found that the above formula had been changed by Appleworks to:-

IF(AP2="BS 2.cwk"!K2,AP2," ")

I tried this out on two further cells only to obtain the same result except that the cell numbers were of course different in each case.

I am sorry that this problem is occurring as in the past I have been able to get over most difficulties with a minimum of assistance. I will look forward to hearing if you have any further advice.

Kind regards,

David Gurney.
David Gurney
 
Posts: 18
Joined: Tue Feb 03, 2004 5:36 pm
Location: Scotland

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby Barry » Sat Jan 29, 2011 11:31 pm

Hi David,

I'm stymied, I think. If you'd like to send me copies of the three files, I may be able to come up with some other ideas. I've sent you a private message with an address to reach me.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby David Gurney » Mon Jan 31, 2011 12:24 pm

Hi Barry,

Thanks for your reply - I am so sorry that you are stymied!

I have as a matter of interest created three new spreadsheets with nothing in them and tried to set them up in the same way as the ones I am having problems with. Exactly the same thing has happened with them so I can only conclude that it must be something to do with the Appleworks spreadsheet program.

I will be happy to send them to you on receiving you e-mail address to see if you can find a solution.

Kind regards,

David.
David Gurney
 
Posts: 18
Joined: Tue Feb 03, 2004 5:36 pm
Location: Scotland

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby Barry » Wed Feb 02, 2011 2:39 pm

David Gurney wrote:Hi Barry,
I will be happy to send them to you on receiving you e-mail address to see if you can find a solution.


Check for this line at the top of this page:
User Control Panel (1 new messages) • View your posts

Click on View your posts.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby David Gurney » Thu Feb 03, 2011 7:32 am

Hi Barry,

Thanks for your advice which I have followed and added a Folder containing the three spreadsheets to the User Control Panel which I hope you will be abe to see.

It's the first time I have done this so I hope all goes well!

Kind regards,

David.
David Gurney
 
Posts: 18
Joined: Tue Feb 03, 2004 5:36 pm
Location: Scotland

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby Barry » Thu Feb 03, 2011 11:17 pm

David Gurney wrote:Thanks for your advice which I have followed and added a Folder containing the three spreadsheets to the User Control Panel which I hope you will be abe to see.

It's the first time I have done this so I hope all goes well!


Hi David,

My bad. I can see your profile, but that doesn't include any folders you've created on your User Control Panel. I told you the wrong button to click. It should have said the "new messages" link beside the count.

Might have been to no avail in any case, as I don't see a copy of the message in my outbox or my sent mail.

I've sent a new message, so you should see "1 new messages"
Click "new messages" to read it.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W

Re: Linking 3 spreadsheets text using Appleworks 6.2.4

Postby Barry » Fri Feb 04, 2011 5:40 pm

Hi David,

I received your message with the three files. 1 and 2 opened successfully, but the third one crashed AppleWorks before it opened on three trials with different routes to open.

I tried to sen that file back to you for you to test, but my mail server returned a non-delivery notice saying it couldn't find the server (in your return address).

Would you please:
Check that your copy of Test 3.cwk opens. If it does, do the following:
Close the file, then locatre it in the Finder.
Select the file, then go to the File menu and choose Create Archive of "Test 3.cwk"
You'll get a new file named "Test3.cwk.zip"

Attach the file to an email message and send me that one.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W


Return to AppleWorks Help

cron
AppleWorks Users Group Logo iWork Users Group Logo