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 ")®EXEXTRACT(sheet2!D2; "\d ")-1
update 2:
=DATE(YEAR(sheet2!D2)-1; MONTH(sheet2!D2); DAY(sheet2!D2))