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...
Two options:
1) table the data
2) create a dynamic range within the formula.
For example:
=LET(
b, $B$2:$B$10000,
nonblank, COUNTA(b),
score, TAKE(b, nonblank),
score / 100
)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 selectedSheetselectedSheet.getRange("B2").setFormulaLocal("=VLOOKUP(E2,'New Sheet'!D:AC,26,0)");// Auto fill rangeselectedSheet.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.
