if statements

Brass Contributor

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
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.

@Jan Karel Pieterse  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.
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,

@Jan Karel Pieterse 

Jan  I am sorry for the inconvenience , But I, believe you are saying is that I would need a column for my projected income, actual income then I would need a individual column for medical projected, medical actual, the difference between the two, and the same for housing expenses, entertainment expenses, pet expenses, etc.
Then set this same thing up for each month
Wouldn’t that be confusing for someone doesn’t work with Excel a great deal. Is there a way you can show me what you are trying to explain.

I think it would be better if you upload a sample of your setup. It just needs one month sheet. Please make sure there is no sensitive data in the sample you upload.

@wjallen14  To have yet another voice chime in here in support of what you're being told: first, you ask a reasonable question, wondering whether creating one single database (that's what it is) from which you run reports using Pivot Table or some other of the more advanced features of Excel, would be confusing to a novice. And the answer is probably affirmative, until you get accustomed to it. At which point, you'll see the advantage. It does require re-thinking how we conceptualize information around, in this case, your budget.

The way you are talking of doing it takes minimal advantage of Excel--essentially you're using the basic math functions (adding, subtracting, maybe some multiplying (if you add in a COLA adjustment for example))....but otherwise you're just using the sheets of an Excel workbook as if it were a green ledger book, nothing more.

What's being suggested is that you re-think the data and how it can be manipulated, sorted, queried, etc., still to do what you want, but so much more, and so much more efficiently.

 

Two things that might help:

  1. If you still want to stick with your original question and approach, I've attached a simple spreadsheet that gives you a simple formula that increments whatever starting month you want by one month in the next cell....that could work across sheets too.
  2. And here's a link to a host of Microsoft Templates for such things as personal budgets. They'll take you a long way toward achieving your objective and might even do it in the way you're conceiving it. https://templates.office.com/en-us/Search/results?query=budget

 

@wjallen14   Depending on how granular you want to be, I have a retirement planning budget that I've created that just looks at things on an annual basis--projected income is on one sheet; projected/actual expenses on another....assumptions and tables on two more. You could easily modify it to a monthly, I think. It would give you yet another way to visualize in contrast with each month on its own. Let me know if you'd be interested.

 

Speaking of granularity, though, with regard to your monthly actuals--are you going to be entering final numbers by category, based on outputs from some other source (Quicken, etc)? Or are you planning on entering each check, as well as each credit card or cash transaction directly into your monthly sheet to calculate Actual very precisely, and also give yourself the ability to dig down into detail when there's a variance that you want to explore. It's this latter sort of analysis that people here on this message thread are getting at by suggesting you create a single database--that will give you the ability to produce high level summary reports on a month-by-month basis, but also provide drill down analytical abilities.

 

 

@wjallen14 

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)+2019

To show both month and year

=TEXT(DATE(INT((SHEET()-1)/12)+2019,MOD(SHEET()-1,12)+1,1),"mmm yyyy")

@Sergei Baklanthank you

@mathetesI want to thank you but I am trying to get my daughter to put information who is not computer savey, once she gets use putting information I will then go to your way thank you.