Feb 08 2021 02:58 PM
Feb 08 2021 02:58 PM
I have many sheets of expenses done for each day of the month and totaled by column for each month and by line for YTD. All individual sheets now are sent to a cumulative sheet where all is shown by and tht is then sent to a Yearly Summary that shows all income and expense for the year for the accountant. I wish to add a sheet to use daily/weekly/or as something occurs for listing income and expenses and have them sent to the proper sheet and date in the workbook so I have to only have one sheet open to enter rather than have to go to each individual sheet to make entries. How can this be done? So far this workbook works beautifully for creating a Summary sheet for the year for accounting, but I wish to make the entry system easier. I need some help with this. Please and Thank you!
Feb 08 2021 07:00 PM
For what it's worth, those images are next to worthless; far too low in resolution to really show anything.
That said, are you in the position to make all of the data collection take place in a single spreadsheet for every day of the year ( and even beyond, i.e., multiple years)? Excel is really really good at taking a single database and producing weekly/monthly/quarterly/annual summaries by expense category,by person, whatever. It's truly a mistake to design a workbook with a different sheet for each week or month, to say nothing of designing it so each day has its own sheet! That actually interferes with enabling Excel to do what it does well.
This kind of bad design begins often because somebody takes what they used to do on paper--when maybe it made sense to track expenses and income on a daily sheet, hand it to somebody to enter into a ledger sheet, which itself was organized by month.... but it doesn't make sense when you're working with a spreadsheet.
So back to my question: are you (and/or your organization) open to re-designing the whole process. I like that you want to make it easier. The way to do that is to start by collecting all data on a single spreadsheet.
Feb 09 2021 01:39 PM
Thank you so much for your reply. You have so far given me more information than I have found anywhere else. Sorry for the bad pics but I tried to send others and could not, so had to reduce but was too far. I hope I can send these with no problems.
Everything in. the workbook. is done so it. is one entry and then everything is moved through formulas to the total sheets that show all income and expenses for the year and. then a final total only that I can take to an accountant. All work wonderfully but I have to go to so many sheets to enter the information I was trying to come up with a sheet that could be added to daily or weekly or whenever there was something to go in. This sheet is not meant to be a daily sheet and was not to be saved but cleared each time used and only would send information to the proper sheet instead of me having to go to every sheet to make the entries.
To you, I am sure this may look cumbersome but having. learned Excel from my own investigations and questions, it is what I have developed and have been trying to. use the best of Excel to make things happen for me with just a single entry. There are so many input items that I created a variety of sheets as you can see from the pictures of the workbook so I could enter only once. It is necessary to enter things daily in some cases and others are only once a month and others are only once a year. I attempted to divide rationally and create sheets that would make this easy for me. It began as a simple process for a small business, then I added personal to it, then two houses, and as it grew I continued to make it less and less cumbersome. Now I was trying to devise a single page that I could use to enter items that would send them to each page so I would not have to open all pages as I enter information. I make sense to you with my thought process.
If you still recommend a complete change then I would certainly need much more help with that but is not out of the question just would rather not have to spend as long as I did to do this workbook to this point before I could use it. I like the easiest and most simple way to get the end result I want which is entering everything once and then having at the end of the year click print and the end result with all totals come out ready for the accountant.
Sorry for the epistle but I do thank you very much for your input and looking forward to your comments
Feb 20 2021 01:16 PM
I really do advise rethinking all of this, ideally beginning with this new year, even though we're some six weeks into it (unless you want to do a different fiscal year).
I'm going to attach a simple example that is taken from the income and expense tracker that I personally use. The heart of it is a single database into which all income and well as expenses in the form of checks and credit card transactions are entered (via downloads from the various banks).
I then use the Excel tool known as a Pivot Table to report or sumarize on ALL of that activity, breaking things out on a monthly basis by budget category.
In your case, you might want to add one or two other ways of slicing this, which would be a matter of added columns to that central database. That would enable you to track income and expenses separately, for example, as they involve different houses (assuming those are themselves income producing properties that you want or need to track for tax reporting purposes).
But the basic idea is to enter all the data into a singe database and let Excel do the heavy lifting of analyzing it (i.e., breaking it apart). Excel is really good at the latter, if you get out of the way.