On google sheets, I have 1 sheet that has a date range selected in two cells for one year, and then on the second sheet I have a date range for the next year. I was wondering if its possible to have:

sheet1!D2 and sheet1!D3 change to match sheet2!D2 and sheet2!D3. So if I select January 2023 in sheet2!D2 it changes the date in sheet1!D2 to January 2022 and then if I select April 2023 in sheet2!D3 it changes sheet1!D3 to April 2022

Tried conditional formatting.

CodePudding user response：

what you describe is called "reference" and it is possible to reference either one or multiple cells.

in sheet2!D2 if you select January 2023 then use this in sheet1!D2:

=sheet2!D2

update 1:

=REGEXEXTRACT(sheet2!D2; "\w  ")&REGEXEXTRACT(sheet2!D2; "\d ")-1

update 2:

=DATE(YEAR(sheet2!D2)-1; MONTH(sheet2!D2); DAY(sheet2!D2))