Forum Discussion
Automatically applying a formula to a variable number of cells based on the value in cells in anothe
Hi, I have a spreadsheet generated daily which has a variable number of rows. I need to add a column to the spreadsheet and add a formula to all cells in that column where a value exists in a cell in...
Thank you for your reply but I couldn't get it to work. Either that or I wasn't clear. Here is my current simple script:
// Set range B2 on selectedSheet
selectedSheet.getRange("B2").setFormulaLocal("=VLOOKUP(E2,'New Sheet'!D:AC,26,0)");
// Auto fill range
selectedSheet.getRange("B2").autoFill("B2:B21", ExcelScript.AutoFillType.fillDefault);
}
This runs well with the pre-defined range "B2:B21" but I need it to work over a variable range which could be B10 or 30,000, or anything in between. I need it to stop when column "E" is empty.
I know this should be simple but I'm not familiar with Script or Macros.
Many thanks
I'm guessing you're using an older version of Excel and receiving a #NAME? error. You could create a traditional dynamic named item for legacy Excel with OFFSET/COUNTA but I think we may have a difference in philosphies. I prefer using worksheet functions for calculation-based tasks.