Forum Discussion
Help with transferring data into different tabs
Hi all,
I am trying to set up a spreadsheet for my family's financials. On the first tab I have all our BILLS (amounts, due dates and from where they are deducted). I then have created numerous tabs titled with the date commencing on each Monday in a weekly format. I wish to be able to enter the information in the BILLS TAB and it appear in the correct date tab. I have some screenshots below:
This shows the BILLS tab and the start of the date tabsThis some of the info from the BILLS tab that I'd like to automatically populate in the relevant date tabs. Only I don't know how to enter the data so I can create correct formulas.I hope this make sense.
Thanks in advance,
Mork
5 Replies
Patrick and Mathetes, thank you so much for your advice. I am very much a novice at Excel and well, computing in general. Wow Mathetes, your elaborate system sounds but way above my capabilities!! I will have a play around with basic table. Thanks again guys; appreciate it. Happy New Year!
Wow Mathetes, your elaborate system sounds but way above my capabilities!!
Don't sell yourself short. I will admit to having a lot of Excel experience (been working with spreadsheets possibly longer than you've been alive--since the early 1980s), but one of the things I've learned is that simple usually works well. That's part of the point I was trying to make.
Learning to use tools like Pivot Table (the link I gave you should help; YouTube has a LOT of instructional videos) makes it fairly easy to hand a well designed and maintained database over to Excel and basically let it do the heavy lifting. One of the mistakes that newbies make is to make too much of it a slightly automated version of what they may have done on paper, rather than taking advantage of the power of Excel. You might find it useful, if you've not already done so, to pass through a good bookstore (or Amazon) and look for a beginner's book on Excel, one that deals with things like budget/expense tracking.
One of the best ways to learn to take advantage of Excel's abilities is -- believe it or not --to play with it in designing an application that you care about. Let's say, oh, how about family budget? Seriously. Working on something that will be helpful to you gives the motivation. You need to give yourself permission, though, to play; by which I mean be free to experiment, make mistakes, work through some trial and error. You might find it easier to being by just tracking expenses.
And for that all you need is a simple and ongoing list of transactions...and, this is important in terms of ease of doing it, you should find it possible to download from your bank(s) and credit card company(ies) their data on your transactions. They may be -- will be -- inconsistent in terms of sequence of columns, but they'll all have the data you need to build a very functional database of all financial transactions. And they did most of the work of data entry (you'll need to add budget categories and subcategories).
I've attached a small spreadsheet this time that shows the headers in my database. And I note again, I download the data from several bank accounts, several credit cards (do a little work to make sure the columns all line up in the consolidated database) and then I add budget info to each line item. It ain't rocket science!
Let me underscore what Patrick2788 has suggested. What you seem to be doing made sense back in the days of paper-based accounting systems. It doesn't make sense with a computer program like Excel at your disposal.
Excel is really good at taking a well-designed database of transactions and producing monthly or weekly summaries on-demand. OR, in the case of a set of financial transactions for the year, the Pivot Table tool is wonderful in its ability to produce a cross-tabulated summary of expenses by budget category and month.
I'm attaching a VERY simple example of what the Pivot Table can do with a long series of financial transactions, both income and expense, with sub-categories of each. I have a much more elaborate system for tracking my family expenses, but it's very personal so I can't share it; trust me, though, it has two levels of budget categories (e.g., Fixed Expenses, with the subcategories of things like Mortgage, Car Payments, Insurance)....and I've tracked expenses from two bank accounts, four credit cards, income and outflow, for at least 10 years. It's all in one raw data database, with two or three "reports" or "output sheets" that can be customized for date ranges or other variables.
It's a design mistake to create a new tab for every week. Seriously. Let Excel do that breaking out on demand for whatever week (or month, or quarter, or year) you want to view.
Can I ask, if I have a bill is due monthly or quarterly, as it has multiple different due dates do I have to add it 12, 4 times to the raw data?
This is all possible with the FILTER function in each sheet but I must ask the question: If all your data is present in one central location, why do you want to move select rows to various sheets?
With the data all in one sheet you can easily filter by the date column or even pivot the data if you need a summary.
