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).
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
Who is Participating?
Can you upload a version of the file with some sample data so we can see how the data is entered?
Thanks
Rob
Thanks
Rob
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
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
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.
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,">="
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