Find and Replace across multiple Rows and Columns

mike.thorpe17421

I have created a calculation sheet that references a number of other sheets for Q3 2022 and is using a formula as follows:

=COUNTIFS({All Countries SSOT Reference Sheet for Das Range 1}, Country3, {Countries SSOT Ref Sheet for Dashboards A- Range 1}, "ALXN 1840", {Countries SSOT Ref Sheet for Dashboards A- Range 2}, ="To be launched", {Countries SSOT Ref Sheet for Dashboards A- Range 3}, >=DATE(2022, 7, 1), {Countries SSOT Ref Sheet for Dashboards A- Range 4}, <=DATE(2022, 9, 30))

I want to create a similar calculation sheet using "Save as New" that then looks for another range of dates for Q4:

=COUNTIFS({All Countries SSOT Reference Sheet for Das Range 1}, Country3, {Countries SSOT Ref Sheet for Dashboards A- Range 1}, "ALXN 1840", {Countries SSOT Ref Sheet for Dashboards A- Range 2}, ="To be launched", {Countries SSOT Ref Sheet for Dashboards A- Range 3}, >=DATE(2022, 10, 1), {Countries SSOT Ref Sheet for Dashboards A- Range 4}, <=DATE(2022, 12, 31))

Firstly does anyone know how I could find and replace the dates on the new sheet for Q4 without manually editing each cell on a specific row and ...

secondly if wanted these 2 sheets to dynamically reference the current and next quarter any thoughts on the code to be used? i.e. Currently in Q3 2022 which references >=DATE(2022, 7, 1), <=DATE(2022, 9, 30) . When we get to 1 October 2022 I want that sheet to automatically update with >=DATE(2022, 10, 1), <=DATE(2022, 12, 31) and then update every quarter thereafter.

Any help much appreciated.

Paul McGuinness

Hi @Mike Thorpe

Swapping out all the dates may not be the preferred option but in the long run could be the easier one.

If you created two summary cells, 1 for the start of the quarter and 1 for the end of the quarter and reference them in your formula instead, then duplicating and updating the sheet takes seconds going forward but will require the pain of updating the formulas initially.

The new formula would look like this:

=COUNTIFS({All Countries SSOT Reference Sheet for Das Range 1}, Country3, {Countries SSOT Ref Sheet for Dashboards A- Range 1}, "ALXN 1840", {Countries SSOT Ref Sheet for Dashboards A- Range 2}, ="To be launched", {Countries SSOT Ref Sheet for Dashboards A- Range 3}, >=[Quarter start]#, {Countries SSOT Ref Sheet for Dashboards A- Range 4}, <=[Quarter End]#)

You are likely going to have to update each formula anyway but if you incorporate the above, you wont have to do it again, and if wanted you could add a formula in the summary cells to update going forward.

Hope this helps.

Paul

mike.thorpe17421

Great idea Paul, thanks very much.

Cheers

Mike