Need to Populate a list of cells increasing a specific formula value by one each time

Hi.

I use an excel spreadsheet to monitor what I wear each day. Odd I know but it works for me

The spreadsheet helps me see if I am not wearing something and thus can get rid of it or perhaps look too use it more. An example of my tops/shirts is attached to show you what I have, but the full version covers ALL my clothes with more detail than shown in my example.

I have now created a separate spreadsheet which gives me monthly and yearly usage of each item using a formula I was assisted with here last year. This spreadsheet was had to be redone from scratch because of a reorganisation of the original spreadsheet, clothes bought/discarded etc and some other bits I needed to change.

What I now need to do is copy the formula from columns C to O Row 4 into each row below but if I use the Duplicate command or pull the list down to Auto populate the cells it changes the cell range which is searched and not the search critera. So the formula:

=COUNTIF('D:\[Clothes-Inventory-Test.xlsx]Shirt Usage'!B24:AG35,"*01*")
becomes
=COUNTIF('D:\[Clothes-Inventory-Test.xlsx]Shirt Usage'!B24:AG35,"*01*")
=COUNTIF('D:\[Clothes-Inventory-Test.xlsx]Shirt Usage'!B25:AG36,"*01*")
=COUNTIF('D:\[Clothes-Inventory-Test.xlsx]Shirt Usage'!B26:AG37,"*01*")

and not

=COUNTIF('D:\[Clothes-Inventory-Test.xlsx]Shirt Usage'!B24:AG35,"*01*")
=COUNTIF('D:\[Clothes-Inventory-Test.xlsx]Shirt Usage'!B24:AG35,"*02*")
=COUNTIF('D:\[Clothes-Inventory-Test.xlsx]Shirt Usage'!B24:AG35,"*03*")

Which is what I actually need. So my question is this: Is there a way to tell or get Excel to increase the search criteria by one each time but leave the search range alone? Or do I need to copy the formulas and then manually edit each cell? Bearing in mind on the example this is 1873 cells and I would have to increase that by around 4 times to cover the other items I have as well so it is not a task I want to do manually at all!

I hope that makes sense and if anyone can help (even if it is to say it can't be done) I would really appreciate it as I have no idea and everything I have tried has come to nothing


Thanks
Andy

Clothes-Inventory-Test.xlsx Clothes-Stats-Test.xlsx

Thank you Pete, I did not realise that the $ sign did that so that is helpful to know.

I will give this a go and see if I can get it working from there.

Edit: I tried this but Excel is returning false results when it finds 3 digit numbers. So if I have a "112" stored Excel is reading it as 11,12 and 112 which is obviously wrong. I tried to change the "00" to "000" to see if that would force it to read 3 digits but returns a result of 0.

Changing the "00" to "0" where there are 2 digits or more returns the same false results as it is still reading the "11" and "12" within the number 112.

Not sure why this is as I thought it would look for a specific number and not a part result.


Many thanks
Andy