Inconsistent treatment of named spreadsheet frames

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

Inconsistent treatment of named spreadsheet frames

Postby Keith Nichols » Sun Aug 08, 2010 9:48 am

How to solve a problem with a spreadsheet frame name in an Appleworks word-processor document. My document consists of 7 word-processor pages. Each page contains a spreadsheet frame. I’ve defined (named) each of the frames thus: “page1”, “page2”, etc. In each of the first 6 frames is a cell containing the total of one of the columns. On page seven, in an unnamed spreadsheet frame, is a cell intended to contain the grand total of these 6 column totals. The formula for this has the form “=[page1]!F28+[page2]!F35 . . .+[page6]!F35” (the cell references are where the column totals appear on the various pages). This formula works fine -- as long as page1 is NOT included. No matter what name I give the frame on page 1 of the document, it doesn’t work like the rest of the frame names, and its inclusion in the formula turns everything to text only. And that’s how it behaves when standing alone, appearing as text, although the software recognizes "page1" as a named frame, containing cells A1 through T50 (this seems to be the maxiumum size of a spreadsheet frame, so all spreadsheet frames start life with this range of cells regardless of how many you choose to display).
Keith Nichols
 
Posts: 20
Joined: Sat Jun 28, 2003 11:15 pm
Location: Dallas

Re: Inconsistent treatment of named spreadsheet frames

Postby Barry » Sun Aug 08, 2010 6:55 pm

Hi Keith,

I also had some difficulty with the formula referencing a cell on another table.

When I entered the formula, whether containing a single cell reference or references to one cell on each of the six spreadsheet frames, the result, as soon as I pressed enter was that the external frame reference was dropped from the formula and only the cell references remained.

I had reduced the size of the actual spreadsheets in the frames to 4 columns by 20 rows, and the result spreadsheet to a single cell (see below), so without the frame names, the formula now referred to a non-existent cell and gave an error message ( #NAME! ), which didn't make immediate sense.

I closed the document (without saving) and started this reply, then decided to try again.

This time I rebuilt the document from scratch, creating one spreadsheet frame, then duplicating it seven times. I put all seven tables onto a single page so that i could see them (and name them) without having to scroll through the pages. I used "one", "two", etc. for the frame names, and added them to the formula one at a time. All went well, as you can see below. The fnal formula is in the entry box at the top of the screen shot.

Image

After seeing it work, I tried a couple of checks.

I renamed frame "one" as "one1", revised the formula to fit, and recalculated. No problem.

I renamed frame "one1" as "one", and recalculated. The formula was displayed as text.
I deleted the "1" in the formula and recalculated. No change—the formula was displayed as text.
I deleted the = sign from the formula, clicked the 'confirm' checkmark (text displayed), retyped the = sign at the beginning of the formula, and clicked the confirm checkmark again. All OK—the result was again displayed.

I opened the result frame and resized it to a single cell. When the cell was selected, the formula displayed as =BADCELL+BADCELL+BADCELL+BADCELL+BADCELL+BADCELL
Apparently the referring spreadsheet has to contain at least enough columns and rows to accomodate the address of the references cell(s).
I resized the result spreadsheet to the same number of rows and columns as the data spreadsheets. The formula was restored and the result displayed.

Hope that helps with your question.

Regarding the closing comment: "...cells A1 through T50 (this seems to be the maxiumum size of a spreadsheet frame, so all spreadsheet frames start life with this range of cells regardless of how many you choose to display)."

Actually, 20 columns by 50 rows is the default size on an inserted spreadsheet, not the maximum (nor the minimum).

To change the actual (as opposed to displayed) size of the spreadsheet, you first have to open the frame (Window menu > Open Frame)

With the open spreadsheet frame as the active window, you'll see the Spreadsheet menu set in the menu bar.
Go Format > Document... and reset the column and row numbers as desired. Click OK.
Close the window to return to the WP document with the inserted spreadsheet frame.

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

Re: Inconsistent treatment of named spreadsheet frames

Postby Keith Nichols » Mon Aug 09, 2010 6:43 am

Barry: I appreciate your prompt response to my message. Further messing about revealed that the frame-reference scheme works only if each frame's name appears in the box labeled "Name" in the Options/Display dropdown menu. My problem with frame1 was that its name didn't appear in this box, while the names of the other five frames did. I don't recall putting them there, so maybe they got picked up from some other menu. So I highlighted frame1 and typed its name in the box, and everything began working as advertised.
Keith Nichols
 
Posts: 20
Joined: Sat Jun 28, 2003 11:15 pm
Location: Dallas


Return to AppleWorks Help

cron
AppleWorks Users Group Logo iWork Users Group Logo