Dec 31 2020 06:49 AM
Dec 31 2020 06:49 AM
Okay, So I know this has been discussed before, but maybe I'm not phrasing it correctly.
I want to store cells based on date values, and when I change the month I want new dates to populate and the cell values from the previous month to be stored somewhere so when I recall that month I get those values.
I've attached a sample workbook.
Dec 31 2020 08:38 AM
Where do those pieces of information come from?
Your sample workbook shows the output you want, but nothing to generate that from; do you have a file of raw data to be summarized? The manner in which you collect that raw data is part of what's needed to determine the way to create this output.
It's highly unlikely that you'll need a macro or VBA routine. Something like this can be easily accomplished using one of several Excel functions, but as noted above without input there can be no output.
I could generate a sample but I'd be guessing at your full situation, so it seems to me to make more sense to ask you what the "bigger picture" is here......
I don't think it's going to be difficult, but the more you can provide by way of background, the more I or somebody else can give you a practical recommendation.
Dec 31 2020 01:45 PM - edited Dec 31 2020 01:50 PM
@mathetesThank you for your Response ! The excel I uploaded was just an example for an issue I've had a few times.
I uploaded a more put together example for you, but basically, Its just a personal budget where I would track income, expenses, etc. The example data provided in the first excel, yes, was identical, but again, this is for future income, so any values would be erroneous. The actually numbers would be input on a weekly basis, but I would like to be able to reference past dates as well.
Let me know what you think,
Jan 01 2021 06:51 AM - edited Jan 01 2021 06:52 AM
So it looks to me as if you may be thinking about this--if you'll excuse the expression, which I'll explain below--it looks as if you may be thinking about this backwards.
I've attached an example of how a Pivot Table can be used to summarize data. In this case, it's only summarizing expenses. I use an approach something like this for tracking income and expenses that my wife and I experience over the years. And the important point--where I think you're approaching it backwards--is that the screen that shows monthly (in your case, weekly) data-- is the OUTPUT end of things.
The raw data is actually transaction by transaction, day by day. In the case of our personal income and expense tracker, I import the raw data from our bank and from each credit card company. It's very specific data, in other words, down to the individual deposits, the individual purchases. This can be used--and the Pivot Table method of Excel offers a lot of flexibility--to create summaries that can display:
Needless to say, it can also do annual comparisons, and so on.
But the key is to think of that tabular display as Output, and the raw data as the Input. You appear to be wanting to do the data entry into your "Income Sheet" and your "Source Data" isn't really what I'd categorize as source data....it's just a way of looking at the calendar. Excel can do that automatically, break down a date, recognizing what month it's in, what week, what day of the week. Familiarize yourself with the many Date functions and you'll see. True source data would be detailed raw data on income and expenses.
By the way, Microsoft has made available a template -- Money in Excel -- that can connect with your bank and credit card companies and do a lot of what you're trying to do on your own. It doesn't work for me--my bank uses dual-factor authentication and MiE doesn't work with that--but might be worth checking out. https://templates.office.com/en-us/money-in-excel-tm77948210
In the meantime, take a look at the example I've attached. As I said, it right now is just an example of how the Pivot Table does what I call the "heavy lifting" of summarizing very detailed data.
Jan 01 2021 01:54 PM
Thanks, im going to figure out a better way to summarize what i'm saying, because its not getting across. Thanks for the effort.
Jan 01 2021 02:22 PM
No. It's getting across. I'm suggesting that you're approaching it the wrong way. You will be much better served for a budget tracking to enter your data into a table and use Pivot Table to see the numbers corresponding to any given month.
Jan 01 2021 04:27 PM - edited Jan 01 2021 04:28 PM
Here's an example of what Pivot Table can do if you enter your data (with fictitious extensions into following months) as a single table. Once the monthly (or weekly, or daily) data have been entered this way, the Pivot Table--usually placed on a separate sheet, but here I did it next to the raw data to show the relationship--can summarize any month. Try changing the month displayed at the just above the tabular display. It immediately shifts.
Here's a resource on Pivot Tables that can help you learn it. It's one of the most popular and powerful features of Excel.https://exceljet.net/glossary/pivot-table