Conditional formatting/formula help

On the second question, are you looking for something like this:

The formula in B2 would be:

=SUMIFS(Data::$B,Data::$A,">"&EDATE($A$1,−6),Data::$A,"<="&$A$1)

The formula in B3 would be:

=SUMIFS(Data::$B,Data::$A,">"&EDATE($A$1,−12),Data::$A,"<="&$A$1)

The -6 AND THE -12 in the EDATE() function tell it to go back that many months from the date it finds in A1 of the summary table.

This assumes you have a true date-time value in A1 and in column A of the Data table.

SG

Glad it helped. As for question 1, the highlighting, you could have cells in column B highlight themselves if they are blank, but I can find an easy way for the cells in column A to highlight depending on the value in B. If it were really important to highlight in A you could add an extra column C (or whatever) with a formula that sets C = A if there is nothing in B, something like this: =IF(B="",A,""). Then select the cells in A and set a Conditional Highlighting rule like this:

(You enter that C1 by first clicking the arrow-pointing-at-a-box icon and then clicking cell C1.)

Then you hide column C.

SG

"I have a couple questions about what conditional format or formula I should use In a logbook.

Column A has "Night Landings", column B has "Night Time"

How can I have a cell in Column A be highlighted in red if a value is entered into A but nothing is entered into the corresponding cell in column B?"

What is the actual data in, say, A3 and B3, when data has been entered in both? Are both empty until data has been entered?

The formatting could be applied to a third cell, located either in column C, or in a separate table placed behind the main table to align with column B.

Here are examples showing both methods.

In this example, Column C is a new column inserted in the table to flag rows where one of the cells in columns A or B is filled and the corresponding cell in the other column is still empty. Text colour in the column is set to a light green for the example, but would be set to match the white background of the cell in practice. The conditional format rule fills the cell with red and changes the text colour to the matching shade of red when the cell contains FALSE.

The second method uses two tables. The image on the left shows BOTH tables, Main and Flag. The image on the right is a copy of Flag.

Main, on the left, contains only the data shown in dark text. The cell fill for column B is set to 'none', allowing column B of Flag, placed behind Main, to be seen through the transparent cells of Main.

The formula in Flag. column B is the same as the one in Column C of the single table above. Because it is referencing cells on a separate table, the table name (Main) is required as part of the address of each cell or range of cells being referenced.

As in the example above, the text colour in Flag::B would be set to match the white background of these cells, and the conditional format rule makes the same changes as in the example above.

Formulas:

Ex.1 C2: =ISBLANK(A)=ISBLANK(B)

Ex. 2

Flag::B2: =ISBLANK(Main :: A)=ISBLANK(Main :: B)

Regards,

Barry

Hi avi8tor4life,

You can do this with an additional column that checks the values in your columns A and B. I used column C for this but it could be anywhere and hidden besides. This solution will highlight the cell in A if there is a entry in only A or B.

The highlighting rule:

quinn

For the record, this will not highlight if there is a value in B but not in A.

q

For the record, this will not highlight if there is a value in B but not in A.

Hi Quinn,

That's because under that condition the text in C is not the same as the text in A. A is empty but C contains a zero length text string.

Regards,

Barry