4

I need to (a) indicate and (b) export several columns of data into a separate sheet, but only the rows that satisfy a certain condition: three columns (which are not exported) must be after today's date.

I'm new to coding, and spreadsheets for that matter, but this is the logic behind it:

If (F2, G2, and H2) are all dates after today, then:
(a) A2 turns green or something, and
(b) Row 2 turns up on Sheet 2

I feel like this has a simple solution but please help!

  • Welcome to the Web Applications site. Please take a look to site's tour. – Rubén May 22 '15 at 0:37
4

For conditional formatting, apply the following to A2: "Custom formula is"

=AND(F2>TODAY(),G2>TODAY(),H2>TODAY())=TRUE

(You can then indicate what the range of formatting should be, e.g., A2:A to get all A column except the header).

To filter the content, use either Query or Filter. Here is an example with FILTER:

=FILTER(Sheet1!A:E,Sheet1!F:F>TODAY(),Sheet1!G:G>TODAY(),Sheet1!H:H>TODAY())

If you place this on Sheet 2, it will pick the columns A-E from the rows where the stated conditions hold.


Note that having the same condition enforced in two different places is not future-proof: someone might change the condition in one place and forget to change in the other. I would add another column to Sheet 1, such as "Export":

=AND(F2>TODAY(),G2>TODAY(),H2>TODAY())

which will contain TRUE if the row is to be exported. (One can use the IF statement to enter custom strings there.) Then both conditional formatting and the filter would use that column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.