CODEWITHSUNDEEP
google-sheets
skeniver
skeniver

Reputation: 2647

Check column for formulas

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?

Upvotes: 2

Views: 231

Answers (1)

Maximilian Peters
Maximilian Peters

Reputation: 31659

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;
}

Upvotes: 3

Related Questions