如何在Excel中基于单元格变更更新日期？（陆军航空NG飞行追踪）

Hey there, let's get this Night Vision (NG) flight compliance tracking set up in Excel so it automatically syncs dates to your Display Panel tab whenever you update column P. I'll walk you through two solid options—one using VBA for full control (great for real-time sync no matter your Excel version) and another using dynamic formulas if you prefer no macros.

Option 1: VBA for Real-Time Sync (All Matching Dates) #

This will automatically refresh the Display Panel tab every time you change a value in column P, showing all dates from column B where the corresponding P value is ≥1.0.

1. Open the VBA Editor by pressing Alt + F11
2. In the Project Explorer (left pane), double-click the worksheet that has your B and P columns (it'll be listed under your workbook name)
3. Paste this code into the code window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only react to changes in column P
    If Not Intersect(Target, Me.Range("P:P")) Is Nothing Then
        Dim srcSheet As Worksheet
        Dim destSheet As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim destRow As Long
        
        Set srcSheet = Me ' The sheet with your flight data
        Set destSheet = ThisWorkbook.Worksheets("Display Panel")
        
        ' Clear old entries in Display Panel (adjust range if needed)
        destSheet.Range("A2:B" & destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row).ClearContents
        
        ' Find the last row with data in your source sheet
        lastRow = srcSheet.Cells(srcSheet.Rows.Count, "B").End(xlUp).Row
        destRow = 2 ' Start pasting from row 2 (leave row 1 for headers)
        
        ' Loop through all rows and sync matching dates
        For i = 2 To lastRow ' Skip row 1 if it's a header
            If srcSheet.Cells(i, "P").Value >= 1.0 Then
                destSheet.Cells(destRow, "A").Value = srcSheet.Cells(i, "B").Value
                ' Add more lines here if you want to sync other columns, e.g.:
                ' destSheet.Cells(destRow, "B").Value = srcSheet.Cells(i, "C").Value
                destRow = destRow + 1
            End If
        Next i
    End If
End Sub

4. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm)—macros won't work in regular .xlsx files!

Option 2: Dynamic Formula (No Macros, Excel 365/2021+) #

If you have a newer Excel version with dynamic arrays, you can use a formula to automatically list all valid dates without macros. Just go to your Display Panel tab, pick a starting cell (like A2), and paste this formula:

=FILTER(Sheet1!B:B, Sheet1!P:P>=1.0, "No valid NG flight dates")

Replace Sheet1 with the actual name of your data worksheet. This formula will automatically update whenever you change column P, and it'll show all matching dates (or the "No valid..." message if there are none).

Quick Customization Tips #

• If your data doesn't start at row 2 (e.g., you have multiple header rows), adjust the row numbers in the VBA code or formula accordingly
• For the VBA option, if you only need the latest valid NG flight date instead of all of them, use this modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("P:P")) Is Nothing Then
        Dim srcSheet As Worksheet
        Dim destSheet As Worksheet
        Dim lastRow As Long
        Dim latestDate As Date
        
        Set srcSheet = Me
        Set destSheet = ThisWorkbook.Worksheets("Display Panel")
        latestDate = DateSerial(1900, 1, 1) ' Initialize with an old date
        
        lastRow = srcSheet.Cells(srcSheet.Rows.Count, "B").End(xlUp).Row
        
        ' Find the most recent valid date
        For i = 2 To lastRow
            If srcSheet.Cells(i, "P").Value >= 1.0 And srcSheet.Cells(i, "B").Value > latestDate Then
                latestDate = srcSheet.Cells(i, "B").Value
            End If
        Next i
        
        ' Update the target cell in Display Panel (e.g., B1)
        destSheet.Range("B1").Value = latestDate
        destSheet.Range("B1").NumberFormat = "mm/dd/yyyy" ' Set date format
    End If
End Sub

内容的提问来源于stack exchange，提问作者Josh Gørdøn