Numbers: Pulling Data From A Report

Here's what it could look like if ultimately you decide, like me, that life is a lot easier taking advantage of the powerful filtering built into Numbers. (It takes only a minute or so to set this up.)

Just two simple formulas:

in F4:

=XLOOKUP(A4,Region Lookup::A,Region Lookup::B,"NA")

In G4:

=AND(B4=A$2,F4=A$1)

Then I clicked on the G column letter and set a Quick Filter:

Now I hide column G and the table will automatically show the correct subset depending on the region and week a enter in A1 and A2.

You get the advantages of a relational type lookup of, say, region, without all the other formulas and setup needed to try to mimic the workings of a relational database "report writer", which Numbers isn't really meant to be!

And you can easily build more powerful logic into the filter by changing the formula in G4 (e.g. perhaps you want to enter ALL in A1 to display all regions, etc.)

SG

Making Numbers behave like a relational database like that can be done with extra columns and somewhat complicated formulas.

But how about considering something simpler?

Start with the Time Card Data Table and add a Region column.

Then when you need a report just do a filter on Data Range and Region.

If you then need that data in a separate place just select the visible cells in the filtered table, command-c to copy, click once in a cell of an existing destination table, and command-v to paste.

SG

You could try this option

I have everything on one sheet, that makes it easier to explain.

Your Timecard Data will get 3 additional columns

Column F is pulling the Region from Rooster, this is done with XLOOKUP based on the name.

Name in Rooster must be unique, if you have multiple identical name it will find only the first one and return the region for that.

Column G will check if the Region and Timeframe are matching your input in Report

Column H will count all "Yes"

In your Report XLOOKUP will pull the data from Timecard Data

Row is defined by the row in Report, I have 3 headers therefor the first data row is row 4.

To get from row 4 to the counter in Timecard Data I need the offset of 3 in the row calculation in XLOOKUP

Your Report must have more rows than potential matches!

You can add a filter to hide not needed rows, makes it look nice.

The support columns in Timecard Data can be hidden after everything is working.

Hope this will help.

Based on your region the , or the ; will be used to separate the different sections of a formula.

Ralf

Blackburn919 wrote:

I'm really trying to learn how to make numbers behave like a relational database.

That can be interesting from a conceptual point of view. I've tried it out myself. But I haven't found where trying to shoehorn a spreadsheet app (either Numbers or Excel) into a relational database schema works well from a practical standpoint. That takes extra columns and tables and formulas to work out and debug. Compare that to the simplicity of keeping your data in a flat file format (e.g., one table). A few clicks in a filter interface and you have your answer in a few seconds.

SG

The ROW function returns the row number of the row containing a specified cell.

ROW(cell)

cell: An optional cell reference to a single table cell. The referenced cell can contain any value, or be empty. If cell is omitted, as in =ROW(), the function returns the row number of the cell that contains the formula.

Examples

=ROW(B7) returns 7, the number of row 7.

=ROW() returns the absolute row number of the cell containing the function.

The formula ROW() is in row 4, therefore it will return the value of 4

In my example I have 3 rows in the header, therefore the first row with data would be row 4

Goal must be that the row 4 give a return of 1, because I want to have the data from Timecard Data that have the 1 in this row

Based on the return of row()-3 I can search with XLOOKUP for the matching value in column H of Timecard Data

It is no issue if Timecard Data has a few hundred / thousand rows.

Your counter in column H Timecard Data will count all matches, even if you have a few thousand of them.

You must make sure that your table Report has enough rows to display all potential matches.

It would be bad if Report has only 100 rows for data but the counter in column H would go to 110, then the last 10 would not be displayed.

Ralf

If you like you can add a check to your Report

You could check that the maximum of matches in column H is not bigger than the number of rows in your Report

In this example I have set the limit to 4, but I have 5 matches in Timecard Data. Therefore you get a warning.

I would count the total number of employees and then create the number of data rows in Report according to the potential max number of matches.

If you set a filter for any column in Report only the really used rows will be shown.

Shown only rows where the text in not "-".

Ralf

Glad that I could help.