Apple Intelligence is now available on iPhone, iPad, and Mac!
Is it possible to reference to all cells of a table excluding the header row?
In Excel you can just reference to "Tablename" and it will dynamically select all cells of the table. This means that you can later add or remove columns and rows and your reference will dynamically change to match all cells of the table (without header row).
If you will be adding new columns, Table 1::1:20 references all cells in all 20 rows of Table 1 (as an example). It will include new columns added anywhere in the table and will include new rows added in the middle of the table. It will not include new rows added above row 1 or below row 20.
If you will be adding new rows, Table 1::A:F does a similar thing.
There is no Table 1 reference that does both things.
If you will be adding new columns, Table 1::1:20 references all cells in all 20 rows of Table 1 (as an example). It will include new columns added anywhere in the table and will include new rows added in the middle of the table. It will not include new rows added above row 1 or below row 20.
If you will be adding new rows, Table 1::A:F does a similar thing.
There is no Table 1 reference that does both things.
Hi Tobist,
I assume that you talk about the Excel Name Manager, correct?
Unfortunately Apple Numbers does not offer this feature.
But you can select a column and the header row information will be ignored.
You just select the letter A to select the complete column in your formula.
As soon as you add new data in column A they will be included in your calculation.
Apple Numbers is not a 100% copy of Excel, some things are easier / better and others are not.
Ralf
Ralf writes: "But you can select a column and the header row information will be ignored."
Whether or not the Header Row is This in the selection depends on the purpose for which the column is being chosen.
When selected as a data source for the SUM function (as in your example) the selection will omit the Header Row(s) and Footer Row(s). This makes in possible to place that SUM in a Header or Footer cell without the SUM(A) formula creating a 'self reference error'.
With other functions (the soon to be forgotten "Lookup", for example) a whole column selection, made by clicking the column reference tab, includes the Header and Footer rows:
Regards,
Barry
Hi Ralf, I am not looking for a name manager. Instead of selecting the whole column I am looking for something to select the whole table (columns AND rows). So that when I later add a new column the selection will still reference to all columns.
I realized that too (opened another post for it), e.g. MATCH selects the header row, but XMATCH does not select the header row when clicking on the column.
Still, can you not select the whole table (rows AND columns)?
Oh, I see, as simple as that, same as in Excel only you cannot reference the table by name.
Reference to all cells of a table by name
