google sheets – Keep cell value same if current month is new month


I have 2 Google sheets.

Sheet 1 imports a cell value from Sheet 2 using the IMPORTRANGE function.

The cell value in Sheet 2 is updated automatically each day, 365 days a year.

Sheet 1 contains data on 1 month only.


I need to create an IF formula using the IMPORTRANGE in Sheet 1 such that the cell value in Sheet 1 keeps the data from the last day of the month if the current date is a new month.

E.g. on February 1, the cell in Sheet 1 needs to display the data from January 31 and not from February 1, even though IMPORTRANGE imports the cell value from Sheet 2 which will contain data on February 1.

Pseudo formula

So, if Sheet 1 is about January, the formula in the cell in Sheet 1 should be something along the lines of,


How do I go about?