Linking/Automating column of text data in Numbers

Question

Level 1

9 points

Linking/Automating column of text data in Numbers

I've never used formulas, and I'm just learning to create multiple sheets / tables in a document.

Imagine a spreadsheet with 1,000 rows for various people, who can be classified into different categories: politicians, athletes, artists, etc. Each row includes a cell that holds a text ID for that individual (e.g. john-f-kennedy).

Now suppose I want to create a copy of this sheet that contains only rows featuring people who are politicians. Each row will have additional columns that add more detail, such as the dates they served in office. The John F. Kennedy row in this second sheet will have that same ID cell. However, I'd like to set it up so that, if I change the text ID in sheet #1, it will automatically change it in sheet #2.

But there's one more step. Rather than manually link 1,000 cells, is there a way to convert an entire column of ID's to something that automatically replicates itself when copied into another sheet or table?

For example, suppose sheet 1 includes three rows with the following text ID's:

john-f-kennedy

hugo-chavez

michelangelo

If I duplicate this sheet, these cells will already be automatically linked to sheet 1. So if I change "michelangelo" to "mickey" in sheet 1, it will also change to "mickey" in sheet 2.

In summary, imagine a master sheet with 5,000 rows representing various people. I then create additional sheets or tables that are subsets of this sheet. If I need to change an ID, I don't want to have to manually change it in every sheet. Rather, I would like to instantly program everything in the column named ID in sheet one, so that it is automatically programmed to update when copied into another sheet.

Can this be done?

Thanks.

Posted on Mar 30, 2022 8:11 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,152 points

Mar 30, 2022 9:14 AM in response to AppleOso

As posted in a replay in your other thread, sheets do not contain a grid of cells. Tables do.

Not sure exactly what you are trying to do. But keep in mind that filters are a powerful and flexible feature that lets you view subsets of your work with very little work (no formulas).

If you haven't done so already, highly recommend having a look in your menu at Help > Numbers Help. Also have a look at the templates at File > New in the menu.

SG

Reply

Answer 2

Barry

Level 9

59,957 points

Mar 31, 2022 1:02 PM in response to AppleOso

"Can this be done?"

As described? No.

Data cannot be 'pushed' from one cell to another, except by methods that require direct action by the user (eg. Duplication of all or part of a Table or Copy all or part of the data on one table, followed by Paste of the copied data into a second table.

You can, however, as suggested by SGIII, Filter the original table to show only the rows matching the data you want to show.

Or you could 'pull' data from the original table into a second table, using a set of formulas that would mark (index) the rows of the master table whose data is to be transferred and, on the second table, to retrieve the data from those marked rows.

From your description, the key to a person's appearance on the second table would be the category to which that person has been assigned.

Question: Will individual persons be assigned to a single category, or could some people be assigned to more than one category?

Regards,

Barry

Reply

Answer 3

AppleOso Author

Level 1

9 points

Mar 31, 2022 1:57 PM in response to Barry

Yes, some people will belong to multiple categories (e.g. politician and businessman). There will also be butilple rows within the same category (e.g. politicians who have been elected to four terms in three positions).

Reply

Answer 4

AppleOso Author

Level 1

9 points

Mar 30, 2022 8:16 AM in response to AppleOso

Note: I just discovered that you can search and replace text across multiple sheets. That would be a good workaround if there are no solutions to my question.

Reply

Linking/Automating column of text data in Numbers

Similar questions