Set default of cell reference to preserve column and row in function Numbers 3.6.1

Question

Level 1

13 points

Set default of cell reference to preserve column and row in function Numbers 3.6.1

I'm using:

Numbers 3.6.1

OSX 10.11.1

For clarity: in the following I use the term "cell address" to mean the Table Name, Row and Column numbers used to specify a particular cell. Please let me know if there is an established name for this.

Am new to Numbers and want to create a database using two tables where most variables in "Table 2" are determined by entries in "Table 1" and vice versa. I am using functions to setup these relationships; however, there is not an obvious geographic relationship between the address of the output cell in Table 2 and the address of the input cell in Table 1, so I always want the cell address called in any function to default to "Preserve Row - True" and "Preserve Column - True" but cannot find anywhere to set this setting. Is this possible?

Thanks in advance your help.

All the best,

RA

iMac, OS X El Capitan (10.11.3)

Posted on Mar 14, 2016 5:47 PM

Reply

Answer 1

SGIII

Level 8

37,556 points

Mar 15, 2016 5:37 AM in response to Rax Adam

It's possible you're trying to do things with Numbers for which it is not designed. It is really good at crunching numbers but it's not a database program. If you look at the templates at File > New in your menu you will find good examples of how to use Numbers. When inputting formulas generally you don't have to do much typing of addresses. You type = to activate the formula editor and click the cell you want to refer to. Numbers then inserts the address for you.

SG

Reply

Answer 2

Yellowbox

Level 7

21,939 points

Mar 15, 2016 5:11 AM in response to Rax Adam

Hi RA,

I always want the cell address called in any function to default to "Preserve Row - True" and "Preserve Column - True" but cannot find anywhere to set this setting. Is this possible?

Not by default, but once you set the Absolute Reference ($ sign) for the formula in one cell, you can Fill Down or Fill Right to other cells.

variables in "Table 2" are determined by entries in "Table 1" and vice versa

No. Formulas are a "one-way street". Formulas can "pull" values from other cells, but can't "push" values into other cells.

Regards,

Ian.

Reply

Answer 3

Barry

Level 9

60,023 points

Mar 15, 2016 12:40 AM in response to Rax Adam

"most variables in "Table 2" are determined by entries in "Table 1" and vice versa."

As Yellowbox says, it's a one-way street. But all of the roads don't need to go in the same direction.

Each cell on Table 1 may contain either an entered value or a value calculated by a formula in that cell, but it cannot contain both.

The same is true for each cell on Table 2.

No formula can be used that makes the value in its cell dependent on the value in its cell, or on the value in a cell whose value depends on the value in the cell containing the formula. If you attempt to use such a formula, t will generate a 'self reference' error message.

But within those restrictions, there's no technical issue with some cells on Table 2 using formulas that pull data from cells on Table 1, and some cells on Table 1 using formulas that pull data from cells on Table 2.

Generally not a useful practice, though.

Regards,

Barry

Reply

Answer 4

Rax Adam Author

Level 1

13 points

Mar 15, 2016 5:37 AM in response to SGIII

SGIII wrote:

You type = to activate the formula editor

Thanks for this, I wasn't aware of the shortcut to the editor, makes sense as assignment. It is at least a faster way to access editing of function content (because in plain text mode), so I can just type the address with the $ instead of having to edit the default afterwards.

Yellow box wrote:
I always want the cell address called in any function to default to "Preserve Row - True" and "Preserve Column - True" but cannot find anywhere to set this setting. Is this possible?

Not by default ...

Thank you, this answers my question: "Preserve Row: True and Preserve Column: True cannot be set as the default". At least now I know not to keep searching for the setting!

Thank you both for your help.

Kind regards,

RA

ps.To clarify on the front of cell-assignment: Barry got my meaning:

Barry wrote:

... there's no technical issue with some cells on Table 2 using formulas that pull data from cells on Table 1, and some cells on Table 1 using formulas that pull data from cells on Table 2.

I wasn't actually asking about that part, I was describing what I have already done. I should have used "some" instead of "most".

Although I disagree with his final assessment, viz:

Barry wrote:

Generally not a useful practice, though.

Everything depends on context!

I've attached an example of the type of 2-table setup I'm working with. No doubt you are all right and a more sophisticated DB program would be appropriate; however, the programmer I hired chose Numbers as the DB and, at least for the moment, I won't be able to migrate to something more powerful / flexible for at least a few months. I'd welcome any feedback you have about the way I have gone about setting up relationships.

Reply

Answer 5

Yellowbox

Level 7

21,939 points

Mar 15, 2016 6:39 AM in response to Rax Adam

Hi RA,

Thanks for the Dropbox link to your document. You wrote:

I'd welcome any feedback you have about the way I have gone about setting up relationships.

Numbers is not a true database. It mimics a database (for example, when sorting a table, rows ("Records") stay together.

I am puzzled by the excessive use of the PLAINTEXT function in the Dropbox document. That is not usually necessary. Numbers uses Data Formats in cells. Here we go with the Numbers Function Browser definition of the PLAINTEXT function:

The PLAINTEXT function returns a string value stripped of any rich text attributes in the input value.

PLAINTEXT(source-string)

source-string: Any value.

Note

This function isn’t available in iBooks Author.

Examples

If cell A1 contains “lorem ipsum”, =PLAINTEXT(A1) returns “lorem ipsum”.

If cell B1 contains “lorem ipsum”, =PLAINTEXT(B1) returns “lorem ipsum”.

=PLAINTEXT(HYPERLINK(“www.apple.com”, “Apple”)) returns “Apple”.

=PLAINTEXT(42) returns “42”.

See also

CLEAN

T

******************************************

PLAINTEXT returns a text string that may not be of any use in other formulas (without other complex formulas that convert it to a value).

Please reply with your overall aim, and perhaps we can help.

And thanks for the green tick!

Regards, Ian.

Reply

Answer 6

Rax Adam Author

Level 1

13 points

Mar 15, 2016 7:11 AM in response to Yellowbox

Yellowbox wrote:

Numbers is not a true database. It mimics a database (for example, when sorting a table, rows ("Records") stay together.

Yes - as I mentioned somewhat vaguely, I hired a programmer to write an app for me that executes a great deal of administrative work. He chose Numbers as the DB because we were already using it for attendance lists (he probably did not expect me to develop the file to this extent, either). It remains to be seen whether or not it will suffice - there is already a great deal of functionality I would like that I know Numbers does not provide, but for the short term, I cannot change programs, so have to make the best of it.

===========

Yellowbox wrote:

I am puzzled by the excessive use of the PLAINTEXT function in the Dropbox document. That is not usually necessary.

The app that calls on the variables defined in that table expects text-formatted entries only. It extracts the plaintext and substitutes it into file names and templates for auto-reply messages / site html / attendance list documents etc. Unfortunately, Numbers does not seem to allow me to designate the final output as text for certain functions (e.g. date-related functions are designated as such), so I have to wrap everything with the plaintext function.

===========

Yellowbox wrote:

Please reply with your overall aim, and perhaps we can help.

My overall aim is to be able to easily create a new row in the "Math 263 Finals" table with a corresponding block of variables in the "Math 263 Finals - info" table (i.e. everything that is colour-matched).

e.g. I could create a 6th session in "Math 263 Finals" whose cells would inherit the functional definitions of the previous row, but with the appropriate variable, and the associated variables in "Math 263 Finals - info" would be similarly easy to create (copy-paste or, even better, automatic upon creation of new row).

I have not been able to work out how to use the default geographic mapping to this end because the pattern is unclear (final formatting of both tables is still being adjusted). Any ideas?

Thank you for sharing your time & thoughts - very much appreciated!

All the best,

RA

Reply