I'm looking for a way to reference an individual cell on a summary sheet across numerous sheets.
The cell is the same on each sheet but just an individual cell. I would obviously like to do this as a formula rather than the long way of x = x
Cheeers
MacBook Pro 13”, 10.14
Hi jd,
Here's a simple example:
There are eight sheets in the document. Seven of these are named following the pattern shown in the third tab above. The first two have had their names edited.
Each Sheet contains one Table.
Each of the tables, when created, had the default name "Table 1". The Table on "Sheet one" kept that name. The names of the others were edited to Table, Table 2, Table 3, Table 4, Table 5, Table 6, and Table 7, giving each table a name that is distinct from all other tables within the document.
Cell C3 on each table contains a number matching the number in that table's name. The image below is of the table on Sheet 5.
With each table having a distinct name, Numbers has no need for the Sheet name in references to cells on these tables.
The formula in column B of Table (on the Summary Sheet) contains the formula shown below that table. That formula is filled down to the row containing "Table 7" in column A.
B2: INDIRECT(A2&"::C3",addr-style)
INDIRECT collects the table name from the same row of column A, then appends the text contained between the pair of double quotes to complete the address of the referenced cell.
The formula then returns the value from that cell.
Names in column a must exactly match the names of the tables containing the referenced cells.
Regards,
Barry
Hi jd,
Here's a simple example:
There are eight sheets in the document. Seven of these are named following the pattern shown in the third tab above. The first two have had their names edited.
Each Sheet contains one Table.
Each of the tables, when created, had the default name "Table 1". The Table on "Sheet one" kept that name. The names of the others were edited to Table, Table 2, Table 3, Table 4, Table 5, Table 6, and Table 7, giving each table a name that is distinct from all other tables within the document.
Cell C3 on each table contains a number matching the number in that table's name. The image below is of the table on Sheet 5.
With each table having a distinct name, Numbers has no need for the Sheet name in references to cells on these tables.
The formula in column B of Table (on the Summary Sheet) contains the formula shown below that table. That formula is filled down to the row containing "Table 7" in column A.
B2: INDIRECT(A2&"::C3",addr-style)
INDIRECT collects the table name from the same row of column A, then appends the text contained between the pair of double quotes to complete the address of the referenced cell.
The formula then returns the value from that cell.
Names in column a must exactly match the names of the tables containing the referenced cells.
Regards,
Barry
Barry! You're a gentleman and a scholar! Thank you so much!
JD
Numbers - indirect reference formula HELP
