dismiss ArrayFormula with simple formulas, doing the same

Question

Hi my name is Ray and Im a junior developer.

Whats is the alternatives to implementing an "array formula" but instead of keeping the hardcode in, it puts in the formula instead???

a       b     (b the formula i want)                                        
100     1     =arrayformula(a2:a / 100)      
200     2     =arrayformula(a3:a / 100)
300     3     =arrayformula(a4:a / 100)
400     4     =arrayformula(a5:a / 100)
500     5     =arrayformula(a6:a / 100)

Now, I want to add columns and want the formula to still continue down for thousands of rows, so doing it manually is not efficient can someone please assist me.

The array formula give me the right answer but pasted is the hardcode not the formula. It also ruins my other formulas that I have.

Is the arrayformula not autofilling because it's not designed that and aggregating into a single cell? — Robin Gertenbach, Sep 27 '16 at 06:47
I think, the OP means he want's simple formulas instead like `=a3/100`, the desired result is 1,2,3,4, but looking like formulas. Am I right? — Max Makhrov, Sep 27 '16 at 06:50
I assume the same but in that case an array formula *would* do the trick, the example doesn't really make any sense as any result in b but the last one will be `#REF!` — Robin Gertenbach, Sep 27 '16 at 06:57
hi guys, thank you for commenting, i just want the last column to appear not the second one, for example if i were to use isformula, the columns would highlight but using arrayformula it is hardcoded and thus isformula will not work — Ray Man, Sep 29 '16 at 02:07

score 0 · Answer 1 · edited May 23 '17 at 12:00

You need a script to accomplish the task. The code is pasted into the script editor:

Tools → Script Editor

You may build onEdit function. Here's very simlified example:

function onEdit(t) {
   var range = t.range;
   var row = range.getRow();
   var sheet = SpreadsheetApp.getActiveSheet();
   var cell = sheet.getRange(row, 2);
   var formula = "=A" + row + "/100";
   cell.setFormula(formula);
}

Here's the one more useful example:

use onedit() trigger on a specific sheet within google scripts for google sheets

There's no way to paste the formula in each row, but only using the script. You may also use simple script, not trigger, and assign it to a button or a custom menu.

dismiss ArrayFormula with simple formulas, doing the same

1 Answers1