Google Sheets：在公式中替换另一公式内命名区域文本的函数需求

Alright, let's tackle this problem head-on. You're right that INDIRECT and SUBSTITUTE alone won't cut it—we need to turn the modified text back into a working formula, not just leave it as static text. Here are two solid methods to make this work smoothly:

Method 1: Custom Apps Script (Most Reliable & Flexible) #

Google Sheets doesn’t let you directly convert text to a functional formula with built-in functions alone, so a quick custom script will fix this. It’s easier than it sounds:

1. Open your spreadsheet, go to Extensions > Apps Script to launch the script editor.
2. Paste this commented code (adjust range references if your Sheet1 structure is different):

function UPDATEFORMULA(recordId) {
  // Grab Sheet1 and locate the row matching your RecordID
  const sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const allRecordIds = sheet1.getRange("B:B").getValues(); // Assumes RecordID lives in Sheet1's Column B
  const matchingRow = allRecordIds.findIndex(row => row[0] === recordId) + 1; // +1 because Sheets uses 1-indexed rows
  
  if (matchingRow === 0) return "Record not found"; // Handle cases where no match exists
  
  // Fetch the original formula text from Sheet1's Column A
  const originalFormula = sheet1.getRange(matchingRow, 1).getFormula();
  
  // Replace every instance of "_old" with "_new" in the formula
  const updatedFormula = originalFormula.replace(/_old/g, "_new");
  
  // Evaluate the updated formula in Sheet2's context and return the calculated result
  const sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  return sheet2.evaluate(updatedFormula);
}

3. Save the script (name it something like FormulaUpdater) and close the editor.

Use the Function in Sheet2 #

In Sheet2’s Calculation column (e.g., cell D2), just type:

=UPDATEFORMULA(B2)

Drag this formula down to cover all your rows. Now, every time you adjust the RecordID in Column B, it’ll pull the correct formula from Sheet1, swap the named ranges, and calculate the result automatically.

Method 2: No-Script Array Formula (For Quick Setup) #

If you don’t want to mess with scripts, you can use a combination of newer Google Sheets functions. Note: This has minor limitations (like needing manual refreshes if Sheet1 formulas change), but it works for simpler use cases.

In the first cell of your Calculation column (D2), enter this array formula:

=BYROW(B2:B, LAMBDA(id, IF(id="", "", EVALUATE(SUBSTITUTE(FORMULATEXT(XLOOKUP(id, Sheet1!B:B, Sheet1!A:A)), "_old", "_new")))))

Here’s the breakdown of what it does:

• XLOOKUP finds the matching formula in Sheet1 using your RecordID
• FORMULATEXT converts that formula into editable text
• SUBSTITUTE swaps every _old suffix in named ranges for _new
• EVALUATE turns the modified text back into a working formula
• BYROW applies this logic to every row in Column B automatically

Pro Tips #

• Ensure Sheet1’s RecordID column has unique values—otherwise, the lookup might pull the wrong formula.
• If your named ranges use a different pattern (not just _old), tweak the regex in the script’s replace line (e.g., use /old(\d+)/g if you have old1, old2, etc.).
• The custom script will refresh automatically when you edit RecordIDs or Sheet1 formulas—no manual refreshes required.

内容的提问来源于stack exchange，提问作者PC McL