Find the first blank cell

Hi j,

Here are two possibilities, both illustrated below. They are independent of each other, and may be used alone or together.

If the students complete the sequences in chronological order, and the dates in the columns are true Date & Time values, you should be able to locate the first empty row using MATCH and MAX.

Since all dates entered will be 'today' or 'before today', a conditional highlighting rule could be used to colour the cell background of any cell where a date has been entered.

Table 1 is the main record. Table 2 reports the next sequence for the student, and the row of Table 1 on which the sequence is listed.

Two formulas are used:

Table 2::B3, and filled right: =MATCH(MAX(Table 1 :: B),Table 1 :: B,0)+1

Table 2::B2, and filled right: =OFFSET(Table 1 :: $A$1,B3-1,0)

This method relies on the sequences being completed in the order listed, as it looks for the most recent (ie. 'largest') date.

The conditional format rule applied to all cells in columns B, C and D of Table 1 fills each cell with a date before tomorrow with yellow, giving a visual indication of where the next vacant cell lies.

This indicator is independent of the order of completion of the sequences, as it depends only on there being a date on or prior to 'today' in each cell to be highlighted.

Regards,

Barry

If you already have each student's log in an individual table for that student, and you want the summary table to list the next flight for each student, then you could try something like this:

The formula in B2 of the Summary table, filled down, is:

=INDEX(INDIRECT(A2&"::A"),COUNTA(INDIRECT(A2&"::B"))+2)

The name in column A of the summary table must match the corresponding table name for that student's log.

Add more student log tables as needed.

There can be no blank cells within the dates listed in the 'Date Completed' columns.

SG

Hi j,

Well, that throws a spanner into the works, so to speak.

COUNTA, used in SG's example, won't count empty spaces, but will count anything in the column that is beyond the first empty space.

MATCH, used in my example, will match a specific value contained in a cell, but won't match an empty cell, or a cell containing a null string.

Some questions regarding the tables shown:

There are four tables shown in the top image. I assume this is the table set used to record completion dates for one student. Is there a purpose for splitting each student's record onto four tables?

In the illustrated configuration, what is the complete cell address for cell B4 on Matt's copy of each of the tables shown in the top image, and for Josh's copy of the Short Brief table. (Trying to get some idea of the full structure here.)

(Sheet-name::Table-name::B4)

Here's an example using a single table for each student. In Matt's table, the 'empty' date cells all contain a single space. Match finds the first 'empty' cell in each date column by searching for a single space.

One issue there is that it makes for a very fragile table. If a date is entered in error, and then removed without replacing the space, the formula will fail to find that cell, should it be the first 'empty' cell in the column.

A partial solution is to use a visible character in place of the empty space—possibly an even more visible one than I chose for Josh's table below. Formulas follow the illustration.

Formulas: The student tables contain no formulas. All columns are entered data, and 'empty' cells in the Completed columns contain either a single space (Matt) or a single hyphen (Josh).

SG's post reminded me that I often default to using OFFSET, mostly because it's familiar to me and usually will do the job. But INDEX + INDIRECT, which I too often neglect is a good choice, too, and one I need to look at more often, so I've used that set along with MATCH here.

There's a single formula used on the Summary table, but the parameters need to be manually changed in each of the cells using it due to the arrangement of the source and summary tables.

Summary::B2: =INDEX(INDIRECT(B$1&"::A"),MATCH(" ",INDIRECT(B$1&"::B"),0))

Match looks for the first space ( " " ) in column B of the table named in cell B1 of its row in its table, then passes the count of rows to get to that value to INDEX. INDEX returns the value in the same row of column A of the same table.

The formulas in B3, B4 and B5 are the same, except for the letters indicating the Completed column and the sequence names for that cell.

Summary::B2: =INDEX(INDIRECT(B$1&"::A"),MATCH(" ",INDIRECT(B$1&"::B"),0))

Summary::B3: =INDEX(INDIRECT(B$1&"::C"),MATCH(" ",INDIRECT(B$1&"::D"),0))

Summary::B4: =INDEX(INDIRECT(B$1&"::E"),MATCH(" ",INDIRECT(B$1&"::F"),0))

Summary::B5: =INDEX(INDIRECT(B$1&"::G"),MATCH(" ",INDIRECT(B$1&"::H"),0))

The formulas in C2 - C5 co through the same set of column changes, but also change the column from which INDIRECT gets the name of the Table to do the searches on. And MATCH is instructed to look for a hyphen ( "-" ) rather than a space.

Summary::C2: =INDEX(INDIRECT(C$1&"::A"),MATCH("-",INDIRECT(C$1&"::B"),0))

Note that the student tables need one extra row beyond the longest list of sequences to present a 0 in the summary table indicating all sequences of that type have been completed (As in the first cell in Josh's column.)

Regards,

Barry

jmac8956 wrote:

I just need the first blank cell that it comes to and the value in the cell to the left of it.

Here is another way you could approach this, preserving your multiple tables for each student, with each student on an individual sheet:

For each log table on a student sheet you add and index column (which can later be hidden) with this formula, filled down:

=IF(B2<>"","",ROW())

If the date is blank on that row this simply shows the row number, which will be used to look up the value in column A, like this:

The formula in B2 is:

=INDEX(Carlos::Dual::A,MIN(Carlos::Dual::C))

This finds the smallest index number in the index column in the Dual table on sheet Carlos and then looks up the corresponding value in column A of that table.

Each cell in the summary table can be filled in like this. For example B3 would be:

=INDEX(Carlos::Mutual::A,MIN(Carlos::Mutual::C))

C2 would be:

=INDEX(Hassan::Dual::A,MIN(Hassan::Dual::C))

C3 would be:

=INDEX(Hassan::Mutual::A,MIN(Hassan::Mutual::C))

etc.

If you don't have too many student sheets you could set up these formulas manually.

Or you can use INDIRECT to construct the addresses for you:

The formula in B2, filled right and down would be:

=INDEX(INDIRECT(B$1&"::"&$A2&"::A"),MIN(INDIRECT(B$1&"::"&$A2&"::C")))

For this to work the sheet names and table names have to match what you have in row 1 and column A of the Next Sequence summary table. You would want to get the formulas working with one student sheet first, then "duplicate" that sheet (via the dropdown beside the sheet name in the "tab" at the top) and rename it to the next student.

SG