Reference cell text in a formula.

[JoaoNYC]

Good evening. I am a newbie in Excel and basically self-taught.
Here is my situation:
I have 5 columns and each starts with a value "2017", "2018", "2019", and so on in row 1.
Under each column, I have several formulas referencing another workbook. This is the formula
=COUNTIFS('01-SALES'!$A:$A,">=1/1/2017",'01-SALES'!$A:$A,"<=12/31/2017", '01-SALES'!$E:$E,"cancelled")
This formula gives me the total number of orders canceled each year.

What I am trying to do is write a formula that I could just copy and paste and it would change the "Year" painted RED in the formula above.
The purpose of all of this is I have a set of queries such as canceled, returned, paid, collection, damaged for 2021 and 2020 but I was asked to do it for the past 10 years.
I could paste the formula and update the date in red to the timespan I want but I am sure there could be an easier way.
Looking at the attached picture my goal would be to create all the columns I need for the previous years by just copy/paste and the only thing I would need to updated would be ROW 1 for each Column with the corresponding year.

Thank you!

 

[FormR]

Hi, welcome to the forum!

Assuming your first year is in cell A1 then you could try something like this.

=COUNTIFS('01-SALES'!$A:$A,">="&DATE(A$1,1,),'01-SALES'!$A:$A,"<="&DATE(A$1,12,31), '01-SALES'!$E:$E,"cancelled")

You could also probably replace the "cancelled" part with a cell reference.