dynamic cell data from one table cell entered into another (variables)

Question

dynamic cell data from one table cell entered into another (variables)

Numbers "Help", including the Apple Numbers webpages with training videos, is so obsessed with explaining how I can choose the colors of my table columns (wow!!! ok, I get it), that it does not explain very well how to implement simple functionality of calculations, functions and variables: the real meat and bones that needs to be made simple. I have been searching endlessly for some tutorial content - anything - on the basic use of variable with tables. My last resort is this forum, so here I am.

Let's say I have four tables. The bottom of each table is a cell containing a sum amount (let's say "sum1" for table one, "sum2" for table 2, etc) reached from adding the values from the rows above. I want to create a fifth table, where each row contains those amounts: sum1, sum2, sum3, sum4. They can be modified dynamically if any change. Then those dynamic values can be added as well. If I think it just requires a variable or "name" given to the source cell (sum1, sum2), which would then be dynamically entered into the last table.

How can I do this?

Thank you!

G5, dual 2Ghz, Mac OS X (10.4.6)

Posted on May 11, 2009 5:29 AM

Reply

Answer 1

Badunit

Level 7

30,708 points

May 11, 2009 6:13 AM in response to Christopher Davis2

The tutorials are geared toward the use of Numbers and its interface, not the basics of using spreadsheets. I assume the assumption made by those who wrote the tutorials was that the user would have some experience with spreadsheets already or get that knowledge elsewhere.

Let's say you have a table called Table 1, the sum on that table is in cell B10, and you wish to have that same sum appear in Table 5 cell C1. In Table 5 Cell C1 you would type =Table 1::B10 . Capitalization and spaces in the name of the table matter. That's the simple answer. You could, instead, just type just the equals sign then click on Table 1 cell B10 to have Numbers create the reference for you.

Reply

Answer 2

May 11, 2009 7:02 AM in response to Christopher Davis2

Christopher,

I know this will sound pretty boring, but the two best resources for learning the basics of Numbers are the Numbers User Guide and the iWork Formulas and Functions User Guide. They are both available as free downloads from a page linked to from the Numbers Help Menu.

Jerry

Reply

Answer 3

May 11, 2009 10:45 AM in response to Jerrold Green1

Ok thank you very much. Badunit, that does create the desired result. It will take some time and practice to figure out this program, and at that point things will seem pretty easy. However in your solution you use a cell coordinate: "B10" for example, as the data source. What if you add or delete a row while designing the table or page layout? Then B10 becomes B9, and the function is broken.

I used to do a little Actionscript with Flash, and there were some very logical, flexible techniques for linking an object with a "name" or variable- and then referencing that anywhere else.

So my follow up question would be (and this is more for arguments sake since your suggestion did work), is there another method utilizing a variable or tag/"name" for a cell, rather than a coordinate, which could be altered?

Cheers-

Reply

Answer 4

Brie Fly

Level 5

7,925 points

May 11, 2009 10:55 AM in response to Christopher Davis2

With some basic trial and error, it's pretty easy to verify that adding/deleting rows, or making block moves of referenced cells will be accompanied by the necessary reference adjustments. That's the way modern spreadsheets (i.e., those released within the last 20 years, or so) work.

Reply

Answer 5

May 11, 2009 11:06 AM in response to Christopher Davis2

Christopher,

Cell references are very tenacious. They will adjust not only if you add a row or column, but also if you move a cell from one place to another in your table.

If you don't want the cell references to track a cell in a changing table, you must use alternate addressing functions INDIRECT, INDEX, OFFSET, etc.

Referring to a cell address by its Header names will not alter the tracking behavior.

Jerry

Reply

Answer 6

Badunit

Level 7

30,708 points

May 11, 2009 11:54 AM in response to Christopher Davis2

Christopher,

As others have answered, Numbers will keep track of that cell (B10) even if you add/delete rows or columns or rename the table or move the table to a new sheet.

If you want to move the cell to somewhere else (such as moving it from B10 to D10), you can do so with the "mark for move" and "move" commands under the Edit menu. Numbers will keep track of it and all references will change to the new location.

Reply

Answer 7

May 12, 2009 6:14 AM in response to Christopher Davis2

Christopher,

This business is so basic and so important that you really should spend some time with a practice document, with the Formula List feature turned on, moving and copying things until you have a good feel for what happens.

Jerry

Reply

Answer 8

May 12, 2009 2:37 AM in response to Badunit

Ok sounds like yes the link will track the cell if its actual coordinate changes. That about covers my issue from this thread. I will continue to explore as new needs come up.

Thanks.

Reply