Find and Replace across multiple Rows and Columns

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.

Answers

  • Paul McGuinness
    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
    mike.thorpe17421 ✭✭✭✭✭

    Great idea Paul, thanks very much.

    Cheers

    Mike

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!