Hi br,
Took too quick a lok at that formula this morning, and missed the comma after -2. That was within the OFFSET function, and signaled a fifth argument, which Numbers read as zero, and sent the error message you read earlier today.
When I got back to this tonight, I decided the best thing to do was to go back to one of the basics in designing a Numbers document: Use as many small, single purpose tables as necessary.
Aux was intended to be such a single-purpose table. Its purpose is to do the calculations necessary to place the Previous Driver name into cell C45 of each of the Day tables. Nothing else.
Kept to that single job, the table needs only three rows—Date, Driver and Count.
For convenience, a fourth row may be used to accept the 'name' of the truck for which the drivers are being reconded, and to contain the short names of the days of the week, as you've done in row 1.
If drivers of more than one truck are to be tracked. additional rows may be added to the table. Each truck will require two rows, one for Driver, the second for Count. As dates are already listed, there is no need for a second row to calculate or show these (or the days of the week). You may want an empty row, containing nothing but the truck 'name' to provide visual separation between records for each truck.
Whatever is being recorded in rows below what is shown, and requires three separate columns where the Driver/Count rows use only one, should be placed on a separate table.
Here's a version of Aux aid out in rows, and containing only the four rows shown in images in your posts.
Row 1 is a Header row, Column A is a Header column. This stabilizes the location of Cell A1, used as the base for the OFFSET function,
Column A is used for labels of each row, and names the type of data found in that row.
In addition to the header column, the table contains a single column to provide a cell reference location prior to the first day of the month. It is necessary because the "Previous driver" is found in a column to the left of the current date, and such a column must be provided for the first date recorded on the table.
And the table contains 31 more columns to provide one column for each day of the longest month.
I have hidden several of the columns of the table for a better fit in the message space here. The hidden columns contain no data other than the dates in August and the weekday names calculated from those dates.
The letters representing driver names were entered manually, as I did not care to reconstruct the tables fom which they would be retrieved.
Aux:
The formula shown above the table is the one used to calculate the driver count. The count is used to lookup in name of the previous driver for any particular date.
This and other formulas on the table should be the same as the ones provided previously (I haven't yet looked back at them). I don't recall providing a formula to retrieve the short day name from the date. If you need it, mention that.
The table below is one I constructed for the purpose of testing the formula to be used in C45 to retrieve the name of the previous driver for any particula date.
The two greyed columns are where I tested the MATCH part and OFFSET part of the formula. The last column contains the full formula (shown below the image in a form that may be copied from here and pasted into your table. Replace the references to A with J1, the location of the date on your Day tables, and place the formula into cell C45 (assumming I recall that corectly) in each Day table.
Test table:
D2: =LOOKUP(MAX(OFFSET(Aux :: $A$1,3,1,1,MATCH(A,Aux :: $2:$2,0)-2)),Aux :: $4:$4,Aux :: $3:$3)
As written ( with MATCH(J1, replacing MATCH(A, ) the formula will insert the previous driver name into the cC45 even when there is no name in cell J1. If you want it to insert 'N/A' instead, place the LOOKUP(...) part above into the space labeled formula below
D2: =IF LEN(B1)<1,"N/A",formula)
Regards,
Barry
