How can I set up a report to record entries per month for the whole year

Hi, I have a spreadsheet that works well but Ihave to set it up for each year by changing the formula from 2017 to 2018.

This is the sample of the formula I am using for my 2017 Rport:

I am using my date column (B) to count how many times something is recorded for each month and I have used rows 7 to 502 as I don't expect to have more than 502 entries for the year.

=COUNTIFS('2017 Register'!$B$7:$B$502,">31 Dec 2016",'2017 Register'!$B$7:$B$502,"<01 Feb 2017")
=COUNTIFS('2017 Register'!$B$7:$B$502,">31 Jan 2017",'2017 Register'!$B$7:$B$502,"<01 Mar 2017")
Up until Dec where I use;
=COUNTIFS('2017 Register'!$B$7:$B$502,">30 Nov 2017",'2017 Register'!$B$7:$B$502,"<01 Jan 2018")

I don't want ot have to do this for each year, I would rather use a drop down menu / box that I can choose the year from and the formula references the year chosen in this cell.

I have attached the spreadsheet that I currently use that does work but I would love something less time consuming.

Thanks in advance. Dot
Amemdment-Request-Register--and--Re.xlsx

In the meantime, try the attached file.

Made amendments on "2017 Report" sheet:
K6 - dropdown of years to choose from. I used Data Validation for this using the List option with list actually hard-coded in the Data Validation; this can be changed to refer to a range of cells if so required.

A7 - create date for 1 Jan using DATE function, based on year chosen in K6, =DATE(K6,1,1)
B7 and down - uses EOMONTH function to create a list of dates for remainder of year.

A7 to A18 then formatted to show only month using custom format "mmm".

Amended formula in B7 to
=COUNTIFS('2017 Register'!$B$7:$B$502,">="&$A7,'2017 Register'!$B$7:$B$502,"<"&$A8)

Copied down the references to column A will adjust to relevant rows.

This is all reliant on dates in column B of 2017 register being entered as proper dates.

How will future iterations of the file be created?
New file, Save As, amend Register sheet??? If this is the case then the reference to "2017 Register" sheet will change when sheet name changes.

Same file, new Register Sheet. If this is the case we will need to refer to the sheet dynamically based on the year selection.
Amemdment-Request-Register--and--Re.xlsx

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 37

Rob HensonFinance AnalystCommented: 2017-12-04

Can you upload a version of the file with some sample data so we can see how the data is entered?

Thanks
Rob

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Dot GlindemannAuthor Commented: 2017-12-04

Hi Rob, I have added some very basic data as you suggested. I hope that this has enough data for you to see what I am trying to achieve. Thanks Dot

Nothing attached

Rob HensonFinance AnalystCommented: 2017-12-05

See attached with your samples.

To be honest, all I was checking was that the entries in column B on the Register sheet were genuine dates so that they can be compared correctly with the dates generated on the Report sheet.
Amemdment-Request-Register--and--Re.xlsx

Dot GlindemannAuthor Commented: 2017-12-05

Thank you for your patiemce Rob. Your solution will help me greatly. Thanks Dot

Rob HensonFinance AnalystCommented: 2017-12-06

You're welcome, glad to be of assistance.

How can I set up a report to record entries per month for the whole year

Who is Participating?