# Reference cell text in a formula.

#### JoaoNYC

##### New Member
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!

#### Attachments

• HELP.jpg
65 KB · Views: 4

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### FormR

##### MrExcel MVP
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.

Replies
6
Views
190
Replies
9
Views
109
Replies
5
Views
201
Replies
6
Views
207
Replies
4
Views
179

1,195,961
Messages
6,012,582
Members
441,713
Latest member
Dave353

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back