I have a Google Sheet with data from companies financial statements in them. I have both quarterly and annual data for them, and so I have a drop down cell at the top of my sheet with a selection of "Annual" and "Quarterly" where the data will then change based on the cell value. I have data for all three statements and ratios which is 150 rows long so it can be aggravating to scroll to the top each time just to change the increment and then scroll back down.

I was looking for idea's from anyone more familiar with Google Apps Script that might know about how I can link four cell values so I only need to change one and all four will change. I would like a drop down in each section but I can still change anyone of them and they all correspond.

Thanks for the help.

CodePudding user response：

Change one and all the others will change

function onEdit(e) {
  //e.source.toast("Entry")
  const sh = e.range.getSheet();
  const loc = e.range.getA1Notation();
  const all = ["A5","A10","A15","A20"];//you can edit and add locations
  const idx = all.indexOf(loc);
  if(sh.getName() == 'Your Sheet Name' && ~idx && e.value ) {
    //e.source.toast('Flag1')
    let oneless = all.slice();
    oneless.splice(idx,1);
    oneless.forEach(l => {
      sh.getRange(l).setValue(e.value);
    });
  }
}