Copy data from one sheet to another based on criteria and text color

Dear @Jordan Streich,

Based on your setup, the Late List can be created as a live view that automatically shows items that are past the Requested Ship Date and not yet completed.

How to set up the Late List

On the Late List sheet, enter this formula in cell A2:

=FILTER(Sheet1!A:F,(Sheet1!E:E<TODAY()) *(Sheet1!F:F <> "Completed"))

I have tested from your link, please check it.

How it works:

Pulls data directly from Sheet1

Shows only items where:

The Requested Ship Date is before today

The item is not marked as Completed

Updates automatically as dates or statuses change

No data is copied or moved, so everyone always works from the same source.

Optional: You can apply Conditional Formatting on the Late List to highlight late items visually, making them easier to spot during daily review.

This approach works in Excel Online and Excel desktop, supports multiple users, and keeps the Late List accurate without manual steps.

• How is the text color applied? Is the text color applied manually, or is it already applied using conditional formatting?
  • Would be applied during entry on Sheet 1.
• Which column determines whether an item is late? Is lateness based on a single date column (for example, Requested Ship Date), or are there multiple conditions involved?
  • Right now we use the Requestion Ship Date. We use other dates during the process but the goal is to have the Item on a Truck shipping to customer on or before Ship Date
• Should items be moved or only displayed? Do you want rows to be physically moved/copied to the Late List, or is it acceptable for the Late List to be a live view that references the original data?
  • Copied to a different list to make locating Late items easier than having to scroll through 100's of lines.
• How should the Late List update? Should it update instantly when data changes, on file open, or only when triggered manually?
  • It would be a combination of sorts, I'd assume. The hope is that when the current date changes it would trigger those items that havent shipped / completed would be moved to a late list and it would be checked daily.
• Are multiple users editing this file at the same time?
  • We currently use Excel online for the company which includes many people having "control" over different areas.
• Which Excel version are you using? Excel desktop app (Windows or Mac), Excel for the web (browser‑based)
  • We currently use both Windows Excel the desktop app and the web based app.

Dear @Jordan Streich,
Welcome to Q&A forum. I understand that you want to create a live Excel spreadsheet where items automatically appear on a new “Late List” sheet when they become late, based on the date and the text color on the original sheet.

I would like to clarify that in Excel, there is an important difference between data and formatting:

Dates, numbers, and text are treated as data.

Text color (such as red font) is treated as formatting only.

Because of this design:

Excel formulas can evaluate dates and values.

Excel formulas cannot read or use text color as a condition.

Formulas also cannot automatically move rows to another sheet.

It means creating a live list based on both date and existing text color cannot be done using formulas alone.

To make sure I recommend the most appropriate and supported approach, could you please help confirm:

• How is the text color applied? Is the text color applied manually, or is it already applied using conditional formatting?
• Which column determines whether an item is late? Is lateness based on a single date column (for example, Requested Ship Date), or are there multiple conditions involved?
• Is text color part of the logic or just visual? Should an item appear on the Late List because it is already colored, or is the color simply a visual indicator?
• Should items be moved or only displayed? Do you want rows to be physically moved/copied to the Late List, or is it acceptable for the Late List to be a live view that references the original data?

Should it automatically be removed from the Late List, or remain there once it has appeared?

• Is duplicate data acceptable? Should the same item appear on both sheets at the same time, or should it exist on only one sheet?
• How should the Late List update? Should it update instantly when data changes, on file open, or only when triggered manually?
• Are multiple users editing this file at the same time?
• Which Excel version are you using? Excel desktop app (Windows or Mac), Excel for the web (browser‑based)

In the meantime, If the Late List only needs to be live based on dates, you can:

Use formulas to identify items that are late based on the date.

Use Conditional Formatting on the Late List sheet to highlight late items in a specific color.

This approach is fully supported, updates automatically, and does not require any automation.

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

I truly appreciate your patience and understanding. If you have any further questions or need further clarification, please feel free to reach out. I'm looking forward to hearing from you. 

Thank you for your cooperation.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.