Why does the table show in the formula and sometime it doesn't

Question

Level 1

22 points

Why does the table show in the formula and sometime it doesn't

Hello,

Attached is a screen shot a an example of my question.

The table may or may not be named. But I always have a header row for my table with a label naming that column. When I click to select the column *and* select the letter of that column I sometimes get the column label and sometimes get the name of the table::name of the column.

The attached screen shot show an example of this. The column letters are not important except for reference so we can call Rate Pay = A and Sick Pay B. When I put a formula in column C, I type out what I need to then allow auto fill to it's part then click A for column and again I click B for column. As you can see in one part just the name of the column is listed and in the other both the table name and the name of the column is listed.

Why? Whats is the difference? I get some differences when dealing with row too. And, just for those who are 'guessing' or opining, this inconsistency appears in all the equations I have used so far.

Thank you.

iMac 21.5″, macOS 10.15

Posted on Feb 19, 2021 10:54 AM

Reply

Answer 1

sirwop Author

Level 1

22 points

Feb 19, 2021 2:46 PM in response to Barry

Berry,

Agreed - however, I clearly did not put enough information in my question. My back ground is in Excel where I make some very clever spreadsheets.

Back on point: So I am working with 1 sheet, three tables, but this question stems from a formula within the same table. Or the formula references cells from within it's own table.

Here is three screen shots as just one example I am seeing in some of the tables I am creating.

In the first one you will see formula reference OT January 7, 2021 then Rate Pay January 7, 2021 then OT January 7, 2021. That is cell numbers H3, C3 and H3.

Once I put that formula in the cell O2, I drug it to auto populate all of column O with the same formula.

But in row 3 and 4 you will see how it used H3, C3 and H3 instead of the full names in the formula (screen shot 2).

Then, in rows 5 - 26 (end of column) it reverts back to the long formula (screen shot 3)

Screen Shot 1

Screen Shot 2

Reply

Answer 2

Barry

Level 9

59,951 points

Feb 19, 2021 2:12 PM in response to sirwop

Numbers needs enough information to locate the cell (or range of cells) being referenced in the formula.

The full address of Cell A1 in Table 1 on the Sheet named Sheet 2 is Sheet 2::Table 1::A1

If the formula referencing that cell is in the same table, only the cell address (A1) is needed.

If that formula is in a different table from that cell, but on the same Sheet, the table name and cell address ( Table 1::A1 ) are needed.

If the formula is in a table on a different Sheet from the table containing that cell, the full address ( Sheet 2::Table 1::A1 )is needed, unless the table has a name distinct fom all other table names in the Document, in which case the Sheet name is not required.

You can always enter all three parts of the address; Numbers will utomatially drop the parts it does not need.

Regards,

Barry

Reply

Answer 3

Badunit

Level 7

28,024 points

Feb 19, 2021 2:50 PM in response to sirwop

I'm not a big fan of using header names as cell references. I turn it off. It can make it hard to debug and hard to read sometimes. Often you end up with a mix of regular references and header references.

When this feature is on, Numbers will try to make cell references based on what is in the row and column headers. If unambiguous it will use the shortest version of it possible. Your two examples are "Rate Pay" and "Table 1::Sick Pay" which I assume are references to entire columns (or rows). You are wondering why one has the table while the other does not. My first guess would be because there is more than one table in the document with the header "Sick Pay" and only one table with the header "Rate Pay". Second guess is sometimes it does strange things.

Reply

Answer 4

sirwop Author

Level 1

22 points

Feb 19, 2021 2:52 PM in response to Badunit

YES!

There are three tables. In table 2 and 3 there is the same header with the same labels in row A1. I will watch for that.

AND How do I turn off the header name feature? I don't like it when building the sheets. The labeling has a place in my sheets but I agree, when building or proofing, I don't like it.

Reply

Answer 5

sirwop Author

Level 1

22 points

Feb 19, 2021 2:56 PM in response to Badunit

LOL. That was nice and quick. I was just entering my search in Numbers Apple Support. Did not get a good result and while thinking about rewording the question, I get your answer. Thank you. I marked this a correct answer for your credit.

Thank you both for your help and answers. Still puzzled why row 3 and 4 did not have the header labels in the formula.

Reply

Answer 6

Badunit

Level 7

28,024 points

Feb 19, 2021 3:01 PM in response to sirwop

If there are two or more cells with the same header names, it will revert to the cell address for those cells.

For example, if there were two cells that would get the name "OT January 7, 2021", it will use the cell address instead. I do not know what rows 3 and 4 should have been so this is just a guess.

Reply