Forum Discussion
Macro that concatenates two cells, references a Date and then copy pastes value into first
wow, thanks JMB17 . beautiful formula.
thanks as well mathetes. for this column i'll probably end up keeping the format as '00 - 00' but i can use the knowledge from your formula as well.
now, i wanted to see if it was possible to have a formula that would list out all the dates that fall in the same month as Today. i figure from there i should be able to finish up this section with index/match.
i'm trying to toy around with the below formulas, but i'm getting either #Value, #Calc or #Spill errors.
is this possible, or am i spinning my wheels?
=FILTER(Data!P:P,VALUE(LEFT(Data!M:M,2)=MONTH(TODAY())))
=FILTER(Data!P:P,LEFT(Data!M:M,2)=MONTH(TODAY()))
=FILTER(Data!P:P,MONTH(Data!M:M=MONTH(TODAY())))
Deleted
As I've said before, I'm mystified by how you're approaching this, unable to figure out the goal.
I can, nevertheless, tell you that the SPILL error with FILTER means something is blocking the function from delivering all the results, not that there's something wrong with the formula. After trying to delete rows and columns adjacent to your own formula, I took your formula and copied it over to a new sheet--you can see the result here.
Clearly some more refinement is needed to the criteria to be applied in the FILTER function.
Deleted
I'm glad you managed. I would like to clarify the point that a Pivot Table is useful not for doing what you've been doing--which (if I understand it) is actually creating the expense records at a granular (by transaction) level. But the Pivot Table is for the purpose of summarizing expenses (or whatever the database happens to be) in a cross-tabulated way. So in mine, for example, all payments for groceries are summarized by month, each month showing a single total for groceries. Same for expenses for gas, car repair and maintenance. The database contains all the details; the pivot table takes those details and shows patterns over time, or patterns by category. Things like streaming audio and streaming video and other mainly discretionary expenses are sub-totaled as Discretionary, even while summarized by those more specific categories.
I create my database by downloading the details of checks written, bills paid, credit card transactions from the various financial institutions. Those all comprise the database. There are thousands of rows of data in a year. The pivot table creates manageable, intelligible summaries.
just finished the entire expense section..current month's bills, next month's and a custom date range...
managed to get it done without a macro or pivot table..just formulas...very happy with it.
much more aesthetically pleasing in my view
- think i got it. might be moving onto the next month's section
i've attached an updated file.
in the Data sheet, range )32:V33 is what i'm trying to accomplish. it looks like the formulas work on the Data sheet, but they don't work when i try to transfer over onto the Money sheet.
On the Money sheet, i can't get column L to transfer over. the text doesn't appear even those the formulas should be correct