Nov 18 2019 12:38 AM
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
Nov 18 2019 03:04 AM
Nov 18 2019 03:37 AM
@Jan Karel Pieterse I use a sheet for each month because the sheet is a budget sheet for each month
Nov 18 2019 03:57 AM
Nov 18 2019 04:04 AM - edited Nov 18 2019 04:13 AM
Nov 18 2019 05:16 AM
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.
Nov 18 2019 05:19 AM
Nov 18 2019 05:46 AM
@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:
Nov 18 2019 09:05 AM - edited Nov 18 2019 09:06 AM
@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.
Nov 18 2019 09:47 AM
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")
Nov 18 2019 02:36 PM
@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.