Numbers pull data from another sheet based on text criteria

KL,

I've done several similar solutions over the years, so it doesn't seem difficult or complex to me. It's a small investment up front, but you'll get exactly what you described. Here are the basic of the approach, and I'll write the expressions for you tomorrow, if you can wait. It's lights out time here.

We will add two columns to the master table, one for "Inactive" and one for "New", and they can be hidden. For each "Inactive" entry the new "Inactive #" column will calculate the number of such entries to that point in the Master Table. Similarly for the "New" entries. In your Inactive Sheet, the first body row will pull in the #1 inactive entry in the Master Table, and so on down the Inactive table rows. Similarly for the New entries.

Regards,

Jerry

In Numbers, sheets contain a blank canvas on which you place tables, charts, and other objects. So by Sheet 1, etc., do you mean Table 1, etc?

There are ways to automate updating a table based on values in another table. But these typically involve the use of additional index columns and complicated formulas to look up values based on those indexes. Most of these approaches are not sort safe.

In my experience what you are already doing is a more efficient approach than trying to make a spreadsheet behave like a database app. It takes a second or two to apply a filter, another few seconds to copy-paste the filtered results into another table.

If your ultimate goal is to calculate counts or sums of subsets of your data, then leaving the data together in the original table and creating a Pivot Table. That only takes a few seconds.

Create a pivot table in Numbers on Mac - Apple Support

Also consider using the powerful Categories feature:

Intro to categories in Numbers on Mac - Apple Support

In the built-in templates at File > New in your menu you can find working examples of each of these approaches: 'Pivot Table Basics' and 'Categories'.

SG

For context, I used to do those database-type hacks myself, and thought they were pretty neat. But that was in the days before I understood how powerful and flexible filters were, and before Numbers adopted Pivot Tables (which have been around in Excel for 30 years, and are true time savers).

I've found that trying to make a spreadsheet act like a database can be fun, but generally doesn't pay off, especially when you count the time debugging the hack and reconstructing it when it breaks, or trying to make it sort-safe.

It is so much easier and, in my experience far more efficient, to take advantage of the powerful features built right into the Numbers interface. Using them, together with good old boring copy-paste, is underrated!

SG

kl,

I'm back as promised. Here are three screen shots, one each for the three Sheets in this project.

As noted, I took a guess at your master sheet layout. To the extent that your master table differs, you will have to adjust the column references.

I've been doing this approach for a long time, and am comfortable with it. The new Numbers features that SG is promoting are a an easier way to get to the results, however the method you asked for isn't terribly difficult and certainly not if you save your work as templates so you're not starting over each time you want to do something like this.

I've tested the solution to see what happens if you sort the Master Table, and it seems robust. If you find an issue, I'm happy to take another look at it.

Thanks for the interesting question.

Jerry