跨多个表的Excel条件

Question

我有一张相当复杂的excel表格。但我可能需要一个简单的解决方案。我的工作簿中有两个工作表，一个包含有接触细节的公司，一个包含事件(由这些公司主持)

// Company Sheet
╔═══════════╦══════════════╗
║ Company   ║ Contact      ║
╠═══════════╬══════════════╣
║ Dominoes  ║ John Doe     ║
╚═══════════╩══════════════╝
// Event sheet
╔═══════════╦══════════╦═══════╦═════════════╗
║ Events    ║ Company  ║ Payed ║    Date     ║
╠═══════════╬══════════╬═══════╬═════════════╣
║Pizza Party║ Dominoes ║  Yes  ║  06-07-2016 ║
╠═══════════╬══════════╬═══════╬═════════════╣
║Pizza Swim ║ Dominoes ║  No   ║  07-07-2016 ║
╠═══════════╬══════════╬═══════╬═════════════╣
║FREE PIZZA ║ Dominoes ║  No   ║  08-07-2016 ║
╚═══════════╩══════════╩═══════╩═════════════╝

现在，使用条件格式，我想给公司工作表上色，并将公司标记为payer或not payer。

查询必须执行以下操作：

• 把公司的每一件事循环起来
• 检查公司是否支付了账单。
• 如果日期不到期，公司不需要支付

因此，对于这个例子，Pizza Party是支付和到期日，因此这将返回FALSE。

Pizza Swim事件未付款，到期。这将返回一个TRUE

FREE PIZZA事件尚未支付，日期尚未到期。这也会在FALSE中返回。

如果公司不支付账单(TRUE)，公司将被标记为red，如果该公司确实支付了他的账单(FALSE)，则该公司将被标记为green。

如果一家公司发生了多个事件，结果TRUE将否决所有其他付费账单(FALSE)。

如何做到这一点？

我试过用一个函数：

=IF(Table2[Company]=A2;IF(Table2[Payed]="Ja";FALSE;IF(Table2[Date]<Today();TRUE;FALSE));TRUE)

Answer 1

我确信有一种更聪明的方法可以使用条件格式和函数来完成这个任务，但是我更像一个VBA的家伙，所以我编写了一个快速的子程序，它可以用一些轻微的调整来做你想做的事情。

Sub MarkNonPayers()
    Dim CompanySheet As Worksheet, EventSheet As Worksheet, Cell As Range, UnpayingCompany As String, FoundCell As Range
    Set CompanySheet = ThisWorkbook.Worksheets("Company Sheet")
    Set EventSheet = ThisWorkbook.Worksheets("Event Sheet")
    UnpayingCompany = "><"
    For Each Cell In EventSheet.Range(EventSheet.Cells(2,1),EventSheet.Cells(EventSheet.UsedRange.Rows.Count,1))

         If EventSheet.Cells(Cell.Row,"M").Value = "No" And CDate(EventSheet.Cells(Cell.Row,"E").Value) - Date(Now) < 0 Then
             Set FoundCell = CompanySheet.UsedRange.Find(EventSheet.Cells(Cell.Row,"A").Value, LookAt:=xlWhole)
           CompanySheet.Range(CompanySheet.Cells(FoundCell.Row, "A"), CompanySheet.Cells(FoundCell.Row,"K").Interior.Color = RGB(255,0,0)
         UnpayingCompany = UnpayingCompany & EventSheet.Cells(Cell.Row,"A").Value & "><"
         End If

    Next Cell

    For Each Cell In CompanySheet.Range(CompanySheet.Cells(2,1),CompanySheet.Cells(CompanySheet.UsedRange.Rows.Count,1))
        If Len(UnpayingCompany) - Len(Replace(UnpayingCompany,"<" & Cell.Value & ">", "")) = 0 AND Not IsEmpty(Cell.Value) AND Cell.Value <> "Total" Then
            CompanySheet.Range(CompanySheet.Cells(Cell.Row,"A"), CompanySheet.Cells(Cell.Row, "K")).Interior.Color = RGB(0,255,0)
        End If
    Next Cell
End Sub

Answer 2

您不需要像下面这样的条件格式规则吗？

=AND(C2="No",D2<=TODAY())