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
=COUNTIFS('2017 Register'!$B$7:$B$502,">31
Up until Dec where I use;
=COUNTIFS('2017 Register'!$B$7:$B$502,">30
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
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
=COUNTIFS('2017 Register'!$B$7:$B$502,">31
Up until Dec where I use;
=COUNTIFS('2017 Register'!$B$7:$B$502,">30
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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
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
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
ASKER
Thank you for your patiemce Rob. Your solution will help me greatly. Thanks Dot
You're welcome, glad to be of assistance.
Thanks
Rob