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 the same row. So I can run this as part of a script without having to reset the range every day, how do I make the formula so it sets against rows with data in them?
Dragging the formula down doesn't work when within the script and the number of rows is variable. ie I need to the formula to work out when to stop adding the formula once the last row of data in the spreadsheet is passed.
Any help gratefully received!
3 Replies
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.
