Forum Discussion
Macro that concatenates two cells, references a Date and then copy pastes value into first
Deleted
Well, as of this writing, your question has 60 views and 0 replies. I'm going to break the pattern. I'm also going to take the risk of trying, perhaps, to explain why nobody has replied.
It looks as if you're trying to create some kind of income and expense tracking workbook. That's admirable. I have one myself that I created. Microsoft even has a very advanced (although also somewhat limited) major product called Money in Excel that you can find by searching here on this website. Money in Excel is a major Excel template; it can draw data from your bank accounts (so long as they're not behind dual factor authentication--the limitation I alluded to in the last sentence).
You are approaching this commendable task--IMHO--in an altogether convoluted way, using brute force (also known as VBA or macro) to accomplish things that Excel can do very elegantly by means of functions calling on a central Table that contains all of your transactions, along with identifiers such as vendor, budget category, etc. There's also the Pivot Table that can create automatically a summary of transactions by category by month (or year); you can click on any cell in the Pivot Table to get a detailed list of the transactions that have gone into the summary number.
In short, Excel can do the heavy lifting for you; you don't need to write macros. But it does require re-thinking the whole design. I'm attaching a very simple example of what I'm describing. Very simple. My own raw data base contains a lot more columns (with identifiers for each row of the credit card (4 of them) or bank account (3 of them); identifiers for budget categories two levels deep)... This example just shows how Excel can take a data table and use the Pivot Table to make sense of the raw data.
thanks a lot for the help mathetes
yeah, i also downloaded Money to see if i could try to come to my solution from that, but that didn't work out. i'll be making use of it down the road though.
part of the reason why i wanted to take this route is because it helps me learn. i'm not too advanced with excel, but i marvel at its power. i created some formulas in here that's i'm pretty proud of, and what i'm trying to do now is likely the most complicated portion of the workbook i'm ultimately trying to create that i hope to use to manage my money, health and investments.
i was hoping to have a macro involved because i wanted to build off it for some of the other things i wanted to incorporate in the file.
i also thought about using a pivot table but as an absolute last resort. i'm hoping it doesn't get to that point. i'm thinking over the next couple months if i can create formulas and a macro that helps me do this and considers different dates i'll not just get the file to a place i love, but also feel more comfortable using excel
thanks for sharing your file though, i can use it for some ideas. and thanks again for the input.
Deleted
Some thoughts in response to what you wrote (which I'll quote in italics)
yeah, i also downloaded Money to see if i could try to come to my solution from that, but that didn't work out. i'll be making use of it down the road though.
I was disappointed in Money in Excel on several points. First, because my bank and stock accounts at Fidelity are all behind dual factor authentication, MiE could not get that data. I had gotten all the credit card data before trying..... But from a sheer design point of view, their budget categories seemed to have been very poorly conceived, and there was only a limited amount of flexibility in modifying them.
part of the reason why i wanted to take this route is because it helps me learn. i'm not too advanced with excel, but i marvel at its power.
Keep at it. You're right about its power. I would just recommend learning to do as much as you can without reverting to macros. The built-in functions are many and varied. Learn to take advantage of them to sort through data. Learn to create tables that reference one another by means of functions like XLOOKUP, INDEX, MATCH, and the like. If you're in the Windows environment, learn Power Query.
Macros are good for recording repetitive actions, and, yes, you might also be able to do some data manipulation. But often I suspect you'll learn later that the data manipulation you can do with a macro is better done, more reliably and faster, with a built in function.
An anecdote to illustrate my point. Years ago (decades ago) I was in charge of the HR database for a major corporation. We had to do a monthly headcount report, summarizing employee counts by division, location, employee category, etc., etc. One of the programmers in IT had written a massive macro that produced the spreadsheet report. It took about 30 minutes to run. I spent some time using nothing but built-in functions and was able to produce accurate results in a small fraction of the time, more reliably. That programmer was simply using the skills she knew and assumed would be more powerful, more suited to the task since it required going through so many records.
That was all on Lotus 1-2-3 and happened in the 1990s..... Excel now is far more powerful than Lotus was then. The data you're working with is far less complicated than what we were working with in tracking transfers, hires, terminations across many thousands of employees. I am suggesting that if you learn how to create well-designed Tables of your transactional data you won't need macros.
FWIW, I have one large workbook that tracks our income and expenses, drawing on data from four credit card companies and three bank accounts. I also have a couple spreadsheets to track investments, one of which is devoted to options trades. No macros are used in any of them. Only built-in functions and tools like Pivot Table. Speaking of which,
i also thought about using a pivot table but as an absolute last resort. i'm hoping it doesn't get to that point.
Why "as an absolute last resort"? You write as if it's a very bitter pill, an unpleasant task to be avoided at all costs. Most people, when they discover how easily it can produce useful cross-tabulated summaries of such things as expenses, are delighted. There's a small learning curve, but it's really small. And well worth delving into. Here: this is a website that does a great job of explaining not only the Pivot Table, but many other built-in functions. https://exceljet.net/excel-pivot-tables
I highly recommend that site (and there are others) as something you can make use of in your learning. YouTube also has some excellent videos, some of them by people you'll encounter on these pages.
thanks mathetes
the main reason for not using pivot tables here is because it wouldn't match the aesthetics i laid out.
however, it doesn't looks like i can accomplish what i'm trying for without using them in place of (at least to my knowledge)
i tried writing out what the formulas would do, but i can't get around it
is it possible to have a formula that does this:
if column N from 'Data' sheet equals the same month as today, then concatenate columns N & O from 'Data' sheet
but with that, it would have to do this search through all of column N without picking the same row twice
i think i might be able to manage from there
