2

I'm looking to be able to check a range to ensure that all the cells in it have a formula; this would let me know if any cells in the column have been altered to static data. In other words, if any single cell in the range is NOT a formula, it should return FALSE.

I tried:

=ARRAYFORMULA(ISFORMULA(A1:A6))

but that still only checks the value in A1. Does anybody know how I could do this?

Rubén
  • 34,714
  • 9
  • 70
  • 166
skeniver
  • 2,647
  • 6
  • 28
  • 34

1 Answers1

3

You can use a custom function(see below). It needs a range as input (but as as a string) and a dummy value to bypass caching, e.g.

=onlyFormulas("A1:A4", E1)

If you want to update the output, you need to change E1.

Custom function

function onlyFormulas(range, dummy) {
  var f = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(range).getFormulas();
  for (i = 0; i < f.length; i++)
    if (f[i] == '') {
      return false;
    }
  return true;
}
Community
  • 1
  • 1
Maximilian Peters
  • 30,348
  • 12
  • 86
  • 99