11-24-2019 06:28 AM
11-24-2019 06:28 AM
I have a sheet with thirteen columns on will say for the year 2019. Now that the year 2020 is coming I would like to two things, I would like a bring to Column 1 which would show a rounding total for the year 2020. But for the rest of the 20 items in each column, I would like to zero them out in the year 2020 and start fresh, This using the same sheet and starting a new sheet. Can this be done???
11-24-2019 09:42 AM - edited 11-24-2019 09:44 AMSolution
Your description of what you have is quite confusing. Would it be possible for you to upload a copy of the existing sheet (without disclosing any private or proprietary info)? THere are people here on these boards who would be happy to help, but it always is far easier to work with the actual example.
But a point of clarification would also be helpful: The title of your post is "using the same sheet" and your next-to-last sentence reads "This using the same sheet and starting a new sheet." Which is it? The same sheet or a new sheet?
11-24-2019 05:41 PM - edited 11-24-2019 05:47 PM
@wjallen14 BIll, you did the attachment just fine. But I am having a hard time connecting your first post with the spreadsheet, connecting it conceptually that is.
Your first post said "I have a sheet with thirteen columns" -- what are those thirteen columns? You do have multiple (but not thirteen) worksheets in the whole workbook...but are those what you're referring to?
As for the rest, well....this looks like a template for budgets that somebody else must have created (else you'd know how to do what you're asking)......
But where is that "rounding total for the year" that you refer to? If it doesn't exist now, say a bit more clearly (with an example from one of your budget categories) what you'd expect to see, and here you'd want it to appear in the Jan sheet for 2020...
11-24-2019 06:35 PM
@mathetesi am sorry if I did not explain my self more clearly, it took me along time to figure how to create tables but I finally figured that out. But what I would like to do is roll the savings and investments over to Jan from December, December being 2019 and Jan being 2020. The rest of the budget which my daughter uses each month, I would like to zero out in Jan and start the new fresh year in all the other categories. But I did not want re created the formulas over again. My daughter is not computer savy. The sheets is what I meant when I first started I am sorry for the confusion
11-25-2019 12:15 AM - edited 11-25-2019 12:17 AM
The first thing I see when I open the schedule is a warning regarding "Circular References". This means that you have built one or more formulae that reference cells or ranges that contain formulae that (directly or indirectly) reference the original cell. You have many formulae like the one in C20 on the JAN sheet.
This is circular reference as you include a reference to C20 in cell C20 itself. And, by the way, this formula is quite useless. It will always return -zero- as it says "if DEC is greater than or equal to -zero- then enter -zero-, otherwise enter -zero-". Much easier to type the number -zero- to begin with.
Should you ever have the need to write a similar formula, keep in mind you don't need all the brackets. "(0)" or "=(0)" are both the same as "0".
In your schedule, tab JAN, cell H12 is another circular reference. The January opening balance refers to the December closing balance in the same workbook, which in turn is derived from the January opening balance plus the monthly transactions in the FEB to DEC sheets.
I commend you with the work you have done so far as you seem to have learned a lot on your own. But, to make a long story short and to avoid unnecessary work on (y)our part, I would advise you to save your 2019 file when the year has ended and then rename it to 2020. Then, simply zero out all the entries from 2019 and manually enter the few DEC 2019 balances you want to carry forward to the JAN 2020 sheet. Change the Year number at the top. Save it again, and you'll be ready for the next year. This will take about three minutes. Less than what it took me to write this response
Hope you find this helpful and good luck!
11-25-2019 08:15 AM
@wjallen14 Bill, I didn't recognize you at first. Just went back and looked at some of the previous Q&A around your task here.
Can I ask how old your daughter is? It would certainly appear that she's old enough to be working at a job, so she's out of school..... It's unusual, as you no doubt know, for the younger generation to be less computer savvy than their parents. So you are to be commended for your own energy here.
I also noted in those earlier exchanges (especially here: https://techcommunity.microsoft.com/t5/Excel/if-statements/m-p/1014389 ) the recommendations you received to use Tables, ideally ONE table for all the input, followed by using Pivot Tables for the output, the reporting. And I can see why, for somebody not computer savvy, you'd be uncomfortable with that recommendation.
I do agree with Riny's last suggestion that you simply create a new workbook for 2020 and manually copy over those very few Year End actual numbers from Dec 2019. It's not as elegant, perhaps, but it's far clearer, and it would enable her to keep a history of 2019.
That said, let me get a bit more philosophical (in an Excel mode) about the task you've undertaken.
Creating a budget or expense tracking spreadsheet can be among the more complex tasks to undertake as one's "first design." So much depends on not only the level of computer expertise but also a lot on how you (or your daughter) intend to USE the resulting workbook.
The way yours is designed right now (as somebody, not I, observed in that earlier exchange) takes minimal advantage of Excel's abilities; on the converse, it asks a LOT of the user. To just take one example, instead of creating an annual budget for Entertainment, you ask her to enter individual figures for each category, Books, Journals, Movies.... and to do so each month.
It would be a better use of the ability of Excel to have a separate worksheet for Annual Budget, where there'd be one number for Entertainment (or you could get more granular and have one for each line under that, but you have 14 high level budget categories already. With just one number for Entertainment for the year, you'd then divide that by 12 for each month and show that as the "Projected Cost" bottom line for that category, and compare the actuals only on the bottom line. And all that would have to be entered each month would be the actuals.
It's of course possible that you intend to make your daughter think more intentionally each month about how much she plans to spend on each of those lines, and then see what she actually spent. But I think it possible that you're unintentionally setting her up either to get totally turned off OR to see the trees (or, worse, the leaves on the trees) but miss the forest altogether.
Did you ever look at the Excel templates for budgets? If you did, you probably were disappointed! I just looked and could not find any (except for ones obviously designed for business use) that asked one to establish a budget or planned amount for given categories, as you are doing here. Instead, all of the personal ones I looked at were basically just expense tracking sheets, comparing income to expenses, which is fine as far as it goes, but I would not dignify them with the lable "Personal Budget" worksheets.
Finally, Bill, for your sake, I need to clarify a point regarding Tables. You have indeed set up many of the sections in each month as Tables. But for functional purposes what you really have (and in some cases it really IS) are lots of Ranges. The latter being a set of rows and columns set off by some fancy borders. A range can be referred to via Naming the Range....but in any event is not a formal Excel Table. You do have most of them set up as Tables, as both the formatting and the little diamonds in many of the headings makes clear, but you're not using them as Tables in the sense that those earlier Q&A exchanges were suggesting. You're using them as Ranges. Which is fine for your purpose, but it's a distinction you need to be aware of. The Tables that various folks were suggesting would have been at most a single table for the entry (input) of ALL actual expenses, and then that same table would be used as the basis for a Pivot Table output report. You would also have a single table where you'd enter the Budget for each category or line item, and those budget numbers could be compared with the actuals for each category or line item. Here's a good description of the distinctive features of Tables and why they're important tools. https://www.excelrush.com/excel-tables-vs-excel-data-range/ I'm really not suggesting--given that you're designing this for your daughter--that you try the more sophisticated Table design that we purists might want, but I am suggesting that (as described at the very end of that link) you convert them all to Ranges: they'll function perfectly well, and you, or your daughter, won't be inadvertently filtering or slicing when that's not really what you want at this stage.
I'm going to take a more in-depth look at your workbook and see if there are some simple improvements I could make. If I do, I'll post the result here.
11-25-2019 09:26 AM
Think of this as a postscript to what I wrote earlier. I did take a more prolonged look at the workbook you've created. It's impressive in some ways--I noticed for example the MANY named ranges that you've created and then refer to in your formulas. At the same time, it is so intricately interlinked that it makes it next to impossible to make some simple changes to take more advantage of Excel: for example, to create a separate Budget tab where on a once per year basis (with opportunity to revise as the year goes on) you'd enter the budget per category.
So instead I went and googled the question "Why budgets don't work." I recommend the results to you.
In particular, I think you'll see many repeated warnings concerning the problem of being too detailed. It sounds like the ideal way to accomplish the objective, but in practice rarely RARELY works. There are also a number of articles resulting from that same search that contain practical recommendations on creating a simpler approach to tracking expenses, ensuring savings..... For your daughter's sake, I sincerely believe that you're far more likely to get her interested by taking a far "higher level" approach, with an emphasis on first saving and investing, maybe just the other categories at the highest level.
11-25-2019 06:33 PM
@Riny_van_Eekelen want to thank you also for allyour help your idea was excellent. I have been trying to student so many different excel manuals and drying to have a formula put the information in a particular cell That I forgot to take some of the formula's out. Again I want to thank you and all contributors for their help. I will go trying to study excel because I really fine it fascinating. If any one as suggestion for more manuals I would be appreciative.
11-25-2019 08:03 PM
You asked for recommendations for Excel reference works. Here's a link at Amazon to one I purchased myself recently. It's comprehensive. AND there are extensive sample spreadsheets on-line that illustrate the many functions the book talks about.
Be forewarned: it's really a big book, and could be overwhelming, but take it slow, use the on-line samples. See if you can come up with a budget template that's better than any of the ones out there already.