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
Dot GlindemannAsked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
Can you upload a version of the file with some sample data so we can see how the data is entered?

Thanks
Rob
1
 
Dot GlindemannAuthor Commented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Rob HensonFinance AnalystCommented:
Nothing attached
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Dot GlindemannAuthor Commented:
Thank you for your patiemce Rob. Your solution will help me greatly. Thanks Dot
0
 
Rob HensonFinance AnalystCommented:
You're welcome, glad to be of assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.