Numbers Lookup months on sheet

Hi NN,

You will need a Table for each month. Each Table should have a distinct name. Your Summary table lists more than 12 months, so I would suggest the name for each month's table be named with both the month name AND the year.

The monthly tables may be on separate Sheets, or the same Sheet. The key, so far as formulas are concerned, is that each Table can be identified by its name.

The example below includes only the table for April, 2016, named "APR 2016" I have assumed that the Totals row will be row 33 on each data collection table, but have written the formula to allow placement of the Totals row in other locations. Rows for most of the actual days have been hidden, and have not been labelled, as these labels play no part in the working of the formula.

TOTALS is defined as a Footer row. The formula, in columns showing a value is SUM(a) where "a" is the letter of the column to be summed. (The two non-zero values have been entered manually for the example.)

The Summary table has a single formula, entered in cell B3, and filled right and down to the last row for which there is a Table whose name matches the label in column A. For the example, the formula has been filled only into Row 3.

B3: =INDEX(APR 2016 :: $A$1:$N$33,MATCH("TOTALS",APR 2016 :: $A,0),MATCH(B$1,APR 2016 :: $1:$1,0),)

Syntax:

INDEX(range, row-index, column-index, area-index)

range: All of the cells in the source table

row-index: the number of the row from which to get the value. This is supplied by the first MATCH statement.

column-index: the number of the column from which to get the value. This is supplied by the second MATCH statement.

area-index: Omitted. Defaults to 1. There is only 1 area to which this INDEX applies—all of the source table.

MATCH(search-for, search-where, matching-method)

MATCH returns the position in the list of the search-for value.

First case:

search-for: the text TOTALS

search-where: Column A of the source table

matching-method): 0 means find the exact value.

Second case:

search-for: the text contained in the cell in row 1 of the specified column. As the formula is filled right, the column increments by one for each step.

search-where: Row 1 of the source table

matching-method): 0 means find the exact value.

The formula must be edited for each new Row. The three references to "APR 2016" must be changed to match the name of the table from which to collect the totals for that row. Once edited in Column A, the formula can be filled right, and will automatically adjust to its new position.

Note: The Duration value in column F of the month's table would not transfer and maintain the same format in column C of the summary table. Any attempt to reset the format to match your example resulted in an error triangle.

Regards,

Barry

Instead of entering your log data in various monthly tables on separate sheets you might consider just entering them in one log (data) table and then use a summary table to pull monthly information from the log table. That is the approach taken in some of the Apple templates (at File > New in your browser). It can greatly simplify life. It is usually much easier to pull information out of one data table than to consolidate information from multiple data tables.

For example your setup could look something like this (I've left out dates "in the middle of the month" and just have a few example dates in two different months).

The data in the total time column was entered as 5h 6h etc. but (to match the way you show it; this format is optional) is given a Data Format of Duration with the colon format.

The formula in C2 that pulls that information into the Summary table by month is:

=SUMIFS(Log::E,Log::A,">="&A2,Log::A,"<"&EDATE(A2,1))

This formula can be filled down the column.

This formula sums values where the date in column A of the log (data) table is on or after the date in column A of the summary table but less than one month after the date in column A of the summary table. (SUMIFS works with column-condition pairs. All EDATE does here is add one month.)

You would have a similar formula for other columns, adjusting the E to point to columns other than the column in which you have Total Time.

Note that the value in A looks like just a month and a day but if you look lower-left you will see it is actually a date-time string that can be used in the formula.

This approach can work across multiple years too.

SG