0

I want to add a timestamp when the result of a cell, which contains INDEX and MATCH formulae (=INDEX('Sheet2'!$C$9:$Q$9,MATCH(I156,'Sheet2'!$C$5:$Q$5,1)), changes. This forumula provides a numerical category/band for my different data entries (1, 2, 3 etc) depending on the number input in column I.

I ran the script below but it only works if I edit the cell itself and not if the formula result changes based on the value of column I changing.

I don't want to associate the script with the cells in column I, which I directly edit, as I want to track the date that my data moves from one category to another and the data in column I could change multiple times before it moves to the next category.

If anyone has some suggestions or links to articles that could help (I am a complete novice) - that would be great!

Thanks,

R


  var row = e.range.getRow();
  var col = e.range.getColumn();

  if (col === 5 && e.source.getActiveSheet().getName() === "Master Test Sheet - All Stores") {
  e.source.getActiveSheet().getRange(row,1).setValue(new Date());
    }
}
OverflowStacker
  • 1,340
  • 1
  • 10
  • 17
R Holmes
  • 1
  • 1
  • Have a look at [How to automatically set date in a cell when a different cell value is changed by a formula from data on another sheet?](https://webapps.stackexchange.com/a/130997/196152) – Tedinoz Apr 16 '20 at 10:46
  • Ditto [Trigger a script when a formula changes a cell value](https://stackoverflow.com/a/42827469/1330560) – Tedinoz Apr 16 '20 at 10:50

1 Answers1

0

onEdit does not track edits made by formulas

  • The installable trigger onChange, that can track SOME formula updates
  • INDEX and MATCH are unfortunately not some of them, however IMPORTRANGE is
  • Thus, you can implement the following workaround:
    • Make a new spreadsheet and import the cell with the INDEX and MATCH formula from your current spreadsheet with =IMPORTRANGE
    • Attach to the new spreadsheet a script and bind it to an onChange trigger
    • Specify within the script whatever you want to happen when the result of your formula updates
ziganotschka
  • 25,866
  • 2
  • 16
  • 33