Dynamically referencing different sheets by changing sheet range argument depending on another cell's value

Question

[Goal]

I want to be able to have a cells with formulas (such as COUNTIFS) that can change the referencing sheet range depending on the value in another cell.

[Example sheet]

To demonstrate, I've created the below example simple Spreadsheet where it has 3 different sheets. 2 with raw data (2022 Data, 2023 Data) and another with a table that will use the both sheets' raw data. https://docs.google.com/spreadsheets/d/1Viz3SUibpaIRu77SLwLxjfcd0ZOVmHPQpu8jTdCL92I/edit?usp=sharing

Cell A2 is for selecting the date (formatted to only show the month) and cell E4 is referencing A2 to get the date/month that was selected. D4 and all month cells adjacent to each references each other to get the incremental months.

Cell range B5:E7 is using the COUNTIFS formula to count how many emails or chats were there for that month. You'll notice that the range argument is referencing as: '2022 Data'!.

What I want to do here is to create a COUNTIFS formula with a range argument that can refer to the corresponding sheet depending on the year of that column. For example for cell B5, the month is Jan 2023, I want it to refer to the 2023 Data sheet instead of 2022 Data sheet in a dynamic way.

Obviously, I could add another COUNTIFS so that it can take 2023 Data sheet's data into consideration, however, I'd have to change the formula every time it's a new year.

Using the QUERY function as an example, I know that you can refer to a cell within the string argument if you use the double quotation and the ampersand symbol (example: "&H1&") to get out of the string. I tried doing something similar (example: '"&B4&" Data'!) but nothing worked.

[Question]

Is there somehow where I could potentially change the cell referring range dynamically depending on the value of another cell?

Answer 1

you can use INDIRECT:

=COUNTIFS(INDIRECT("202"&RIGHT(B4, 1)&" Data!A:A"),B4)