Permanent 'links' across Numbers sheets

Can't you do this with some kind of LOOKUP()?

Assuming on Sheet you you enter the NAME of the book in some cell (e.g. A2), rather than setting A2 to reference the inventory directly (e.g. '=Inventory::A23")

Now the other cells in the reading list can be set to LOOKUP() the current location of the book name in the inventory and update accordingly:

For example, here's a sheet with an 'Inventory' table and a 'Reading List' table.

(clearly I don't have your data, but I'm just using example data to show the idea)

Now, on the reading list, you add the date in column A, and in column B you enter the name of the book you're looking to read.

The 'magic' is in column C. The formula for cell C2 is:

=XLOOKUP($B2, Inventory::$A, Inventory::B, "", 0, 1)

This translates as 'take the value in cell $B2 and go find it in column $A in the inventory table using an 'exact match' search. When you find it, take the corresponding value from column B on the inventory table'.

The $ signs in this are important since they 'lock' the formula to the relevant columns. That way you can fill the formula across and down the Reading List table and it will still perform the correct lookup, but taking values from the Pages and Location columns as appropriate.

With this model you can update the inventory table with new books, and even change their order, and the reading list will automatically maintain the right links, since it's looking for the book name as the key.

Mark Sealey wrote:

I have a plan of study in Numbers1. . Each row in Sheet 1 contains the author, title and details of a book to be read. Those are - if you like - an inventory of what I have awaiting me. Independent of any order in which I shall read them

2. On another sheet I have the order in which I want to read these books independent of how I have entered them in my 'database' (#1 above)

3. What I have on Sheet 1 gets sorted each time I add a book - in alphabetical order; so the rows are constantly changing positions

Rather than scatter your data in multiple places in your document, have you thought about using the tools built into modern spreadsheet software like Numbers to address common tasks like this?

Alphabetize or sort table data in Numbers on Mac - Apple Support

Filter data in Numbers on Mac - Apple Support

The idea is to keep the data in one place and use the power of the software to give you different "views" into the data.

BTW, where you write "sheet" I think you mean "table." A table in Numbers is the object that has cells in rows and columns.

SG

If you are moving the cell to another place in the same table or to another table on the same sheet,

1. Click on the cell (i.e., select it)
2. Click and hold on the cell until it lifts out of the table
3. Set it down somewhere else

You can do this same thing to an entire column or row or to a range of cells. If you drop it on an empty spot on the canvas, it will create a new table from it.

There are no cell references that will do that. A few other ideas come to mind:

Get rid of Sheet 2, do it all in Sheet 1.

1. Add a new column to Sheet 1 that has numbers for the order in which you want to read the books. The other books will have nothing in this column.
2. To create a reading list, put a filter on that column to show only rows that are not blank. Sort by that column.
3. Turn the filter off when adding new books to the database or when selecting books for your reading list. Turn it back on to see your reading list

Or,

Keep both your tables but instead of creating references to cells in Sheet 1, copy/paste the whole row of cells to Sheet 2. This way you'll have a copy of the actual text from those cells instead of references to them.

Or,

Give every book in your database a unique identifier. Use XLOOKUP to get the data from the row.

1. Unless you have a column that already has a unique identifier/code, add a new column to the database and create/enter a code. It could be the ISBN number or a number that is the order in which you entered the books, or a concatenation of title-author. Whatever you figure out.
2. In a new column of Sheet 2, you will enter (or copy paste from the database) the unique identifiers for the books you want to read.
3. The rest of cells in each row will use XLOOKUP to look up the rest of the information for the books.

You may want to use a unique identifier for each book because book titles are not necessarily unique.

You may want to use XLOOKUP instead of LOOKUP because you can specify exact match only and it handles cases where there is no match.

> You may want to use XLOOKUP instead of LOOKUP because you can specify exact match only and it handles cases where there is no match.

I said LOOKUP() but actually used XLOOKUP() in my example. Old habits die hard :)