Google Sheets跨表数据验证联动：保留下拉菜单实现状态同步求助

我需要实现：当Sheet 2（「Courses | Client Review」）的「Client Remarks」下拉选项变更为「Finalized By Client」时，将Sheet 1的「Course Status」同步设置为该值。但使用数组公式=ARRAYFORMULA(IF('Courses | Client Review'!A4 ="Finalized By Client","Finalized By Client",))后，「Course Status」的下拉菜单消失了，希望找到既能同步值又保留下拉功能的方案。

Got it, let's first clarify why your dropdown vanished: when you use an ARRAYFORMULA to overwrite an entire column, Google Sheets clears any existing data validation rules (your dropdown menu) because array formulas and data validation can't coexist on the same cell range. Here are two reliable fixes to get both functionality and your dropdown back:

方案一：单元格公式 + 手动保留数据验证（无需代码） #

This is the simplest approach if you don't mind a quick drag to apply formulas:

1. Restore your dropdown first
   Select the range in Sheet1's "Course Status" column (e.g., D4:D), go to Data > Data validation. Set the rule type to List of items, input your original dropdown options (including "Finalized By Client"), and check "Show dropdown list in cell" to bring back the menu.

2. Add the sync formula to individual cells
   In the first target cell (e.g., D4 in Sheet1), paste this formula:

   =IF('Courses | Client Review'!A4="Finalized By Client", "Finalized By Client", "")
   

   Then click the small square at the bottom-right of the cell and drag it down to apply the formula to all relevant rows.

3. How it works

   • When Sheet2's "Client Remarks" is set to "Finalized By Client", Sheet1's corresponding "Course Status" cell auto-fills with that value.
   • For other cases, the cell stays blank, and you can still use the dropdown to select other options. The dropdown menu remains intact because we're not overwriting the entire column with an array formula.

方案二：Apps Script自动同步（全自动化） #

If you want full automation without dragging formulas, use a simple on-edit script that syncs values without touching your data validation:

1. Open the script editor
   Go to Extensions > Apps Script in your Google Sheet.

2. Replace the default code
   Delete the placeholder code and paste this:

   function onEdit(e) {
     // Get details of the edit event
     const editedSheet = e.source.getActiveSheet();
     const editedCell = e.range;
     const editedValue = e.value;
     
     // Only trigger if editing Sheet2's Client Remarks column (A column here)
     if (editedSheet.getName() === "Courses | Client Review" && editedCell.getColumn() === 1) {
       const row = editedCell.getRow();
       // Target Sheet1's Course Status column (D column here—adjust the number if needed)
       const targetSheet = e.source.getSheetByName("Sheet1"); // Replace with your Sheet1 name
       const targetCell = targetSheet.getRange(row, 4);
       
       if (editedValue === "Finalized By Client") {
         targetCell.setValue("Finalized By Client");
       } else {
         // Uncomment the line below if you want to clear the status when Client Remarks changes
         // targetCell.clearContent();
       }
     }
   }
   

3. Save and test
   Click the save button, name your project (e.g., "Status Sync"), then head back to Sheet2. Change "Client Remarks" to "Finalized By Client"—Sheet1's corresponding "Course Status" will update automatically, and your dropdown menu stays right where it is.

Quick notes for the script: #

• Adjust the column numbers if your "Course Status" is in a different column (e.g., use 5 instead of 4 for column E).
• If you want to clear the "Course Status" when "Client Remarks" is changed to something else, remove the // from targetCell.clearContent();.

内容的提问来源于stack exchange，提问作者Kyle Cole