Forum Discussion
if statements
I have 12 sheets one for each month of the year. On December on want to show just the year (2019) inA1 then in January I want to show the year (2020) in A1. I would like to do this using and if statement. then for the next 12 months show the year (2020) and then again January show 2021 since December will be 2020 and do this for next five years can this be done
11 Replies
How to organize data better it depends. On volume and granularity of data and on goals of course. As for direct question - if your sheet starts from Jan 2019 and after that go sequentially, to show year in the A1 you may use
=INT((SHEET()-1)/12)+2019To show both month and year
=TEXT(DATE(INT((SHEET()-1)/12)+2019,MOD(SHEET()-1,12)+1,1),"mmm yyyy")SergeiBaklanthank you
- Hello
As the highly experience and respected Excel MVP, Jan Karel advised, there is no need to have 12 sheet tabs for records for each month. All you need to do is have a single sheet and combine all the information from Jan to Dec. Then, you can use the myriads of functionalities that is available in Excel to analyze your data.
...and that's the "Excel way" of working with spreadsheet.
Regards, - Why use a sheet for each month? Excel makes your life a whole lot simpler if you put your data into one long table with a date column. If you do that, you make summarizing your data a lot simpler.
JKPieterse I use a sheet for each month because the sheet is a budget sheet for each month
- That in itself is not a very good reason for putting the info an a separate sheet for each month if you ask me. I promise you, reporting becomes very simple of your data is on one sheet. You can then use a pivot table to summarize very easily. And if you want to view one month you can filer your table to that month.
