Mac Numbers functions

ARC1995 wrote:

What I want to learn how to do is create a new to do list with similar headers that searches the master list for the due date, and based on the due date, brings all the data from that row to another table.

That's somewhat complicated. Much simpler would be to use the built-in filtering capability to filter for a given date. Have you tried that?

SG

ARC1995 wrote:

I want to set up one master list that I can continuously add things to with dates attached and have a separate list that will pull the data from the row with that date.

This way I can change the date on a to do list and the list will automatically fill up with my tasks.

Easily done with a simple filter and separate one-cell table:

Just change the date in the box and the list automatically fills for that date. After it's set up no need to go the right panel.

SG

Hi ARC,

You wrote: "

"Even if it is complex, I would like to know how to do it the way I originally stated.

please let me know if you can help with that."

In order to do this in the manner you originally stated, you will need:

An index column on the data table (master list) in which you will need a formula that contains a distinct index value on each row of the table. The simplest index is one that contains the Date value followed by a serial number indicating how many times that specific date value has appeared on the list in cells from the tp of the table to 'this row'.

To find the rows containing this date, you need a lookup function that can be set to require exact matches between the value it searches for and the matching values in the index table of the table it is searching. VLOOKUP can do this, as can a combination of MATCH and INDEX. (The new XLOOKUP function is also a candidate, but was introduced only recently, and is not available on the earlier version of Numbers that I am using.)

The lookup (or match) function needs to be supplied with a search value consisting of a specified date and a serial number between 1 and the number of times that date is found in the Date column of the data table (COUNTIF is your friend here). ROW() minus the number of rows above the first row of the 'today's list' table will provide the needed serial numbers. COUNTIF will tell the formula, wrapped in an IF statement, when to stop bringing in data from the data table.

Doable, but complicated.

SGIII's filter suggestion is much simpler, and provides much the same results. In it's two table version, it's also easy to modify to show TODAY's tasks plus those due tomorrow or the day after.

As presented, SG's single cell 'trigger' will (when the filter is turned on) show ONLY the rows sharing the date entered in the Date table's single cell. Turning the filter off will show the whole table.

The current version of Numbers may display the filter's on-off switch without the need to open the Sort and Filter section of the right sidebar, If not, a small change in the formula shown in SG's last post above can make emptying the single cell in the date table cause the full table to be shown when you want to add new dates and tasks to the list:

Current: E3=Date::$A$1

Revised: OR(ISBLANK(Date::$A$1,E3=Date::$A$1)

This will show all rows of To Do when Date::$A$1 is empty, and will show only the rows containing 'that date'

when Date:: A1 contains a date.

Regards,

Barry