I need help with my Excel script

Question

I have been trying to automate excel for my job. Here is some context: there's 2 sheets titled 'Sheet1' and Prisma. I need to use Prisma to fill in the columns in Sheet1. Here is additional context.

• column c in sheet1 matches to column L in prisma
• column f in sheet 1 matches to AB in prisma
• column h matches to AS in prisma
• the common identifier for both sheets is column G in Sheet 1 (which is column AA in Prisma)
• i want these xlookups to only rows between 1798 to 1930
• I only want the second and third portion of the XLOOKUP to be frozen. Column G, the first part, CANNOT be frozen
• for example: =XLOOKUP(G1798, Prisma!$AA$2:$AA$18758, Prisma!$AB$2:$AB$18758)

this is the script I've developed but all it does is cause all rows to match row 1798. I'm not sure why.

function main(workbook: ExcelScript.Workbook) { 

  // Define Sheet1 and Prisma 

  let sheet1 = workbook.getWorksheet("Sheet1"); 

  let prisma = workbook.getWorksheet("Prisma"); 

  let numRows: number = 1930 - 1798 + 1; // Number of rows from 1798 to 1930 

  // Apply XLOOKUP formulas 

  for (let i: number = 0; i < numRows; i++) { 

    let rowIndex: number = i + 1798; // Adjusted row index 

    let xlookupFormulaC = `=XLOOKUP(Sheet1!G${rowIndex}, Prisma!$AA$2:$AA$18758, Prisma!$L$2:$L$18758)`; 

    let xlookupFormulaF = `=XLOOKUP(Sheet1!G${rowIndex}, Prisma!$AA$2:$AA$18758, Prisma!$AB$2:$AB$18758)`; 

    let xlookupFormulaH = `=XLOOKUP(Sheet1!G${rowIndex}, Prisma!$AA$2:$AA$18758, Prisma!$AS$2:$AS$18758)`; 

    sheet1.getRange(`C${rowIndex}`).setFormula(xlookupFormulaC); 

    sheet1.getRange(`F${rowIndex}`).setFormula(xlookupFormulaF); 

    sheet1.getRange(`H${rowIndex}`).setFormula(xlookupFormulaH);} 

  // Freeze formulas 

  sheet1.getRange(`C1798:H1930`).copyFrom(sheet1.getRange(`C1798:H1798`), ExcelScript.RangeCopyType.formulas);}

Answer 1

Hello Rahima,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

The issue with your script causing all rows to match row 1798 arises from the last part of your code, where you're copying formulas from row 1798 to the rest of the rows in your range (C1798:H1930). This action overrides the individual XLOOKUP formulas you set up in the loop with the formula from row 1798, hence causing all rows to match row 1798.

To fix this, you simply need to remove or comment out the following line from your script:

sheet1.getRange(C1798:H1930).copyFrom(sheet1.getRange(C1798:H1798), ExcelScript.RangeCopyType.formulas);

This line is not needed because your loop already sets the correct formula for each row individually. Once removed, your script should correctly apply the unique XLOOKUP formula to each row from 1798 to 1930, referencing the appropriate cells in Sheet1 and Prisma without causing all rows to match the data from row 1798.

I hope this helps.

Best Regards, Ibhadighi