Circular dependency error in Google Sheets-CodePudding

I have two formulas:

CELL W21:

=IF(AND(TODAY() >= DATE(2022,2,1), TODAY() <= DATE(2022,2,28)), SUM('Master Report'!$B$20:$B$24,'Master report'!$B$31:$B$32), W21)

CELL X21:

=IF(AND(TODAY() >= DATE(2022,3,1), TODAY() <= DATE(2022,3,31)), SUM('Master report'!$B$20:$B$24,'Master report'!$B$31:$B$32), X21)

The first formula works ok, while the second gives a circular dependency error. I want the value in X21 to be populated only between those dates but I want to set up the spreadsheet now for the entire year.

The values in "Master Report" gets updated automatically via API on 1st of every month and I am referencing those value in a table where each month is present. Example as follows:

Master Report (updated automatically every 1st of the month) | Page | Visits | | -------- | -------------- | | Page 1| 20012|

This spreadsheet | January 2022| March 2022| April 2022| |:---- |:------:| -----:| | Visits| Visits| Visits|

CodePudding user response：

I managed to do this in Google App Script as suggested by @TheWizEd.

It's a very primitive script, but I hope it can help people with this problem in future:

function checkDateAndWrite(dateStart,dateEnd,cellFrom,cellTo) {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var reportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master Report')
  var today = new Date();
  var dateOne = new Date(dateStart);
  var dateTwo = new Date(dateEnd);

  if(today.valueOf() >= dateOne.valueOf() && today.valueOf() <= dateTwo.valueOf()) {
    activeSheet.getRange(cellTo).setValue(reportSheet.getRange(cellFrom).getValue());
  }

  return;
}