Trice
New Member
I've used Excel for my personal finance tracking for 15 years but there has always been one issue that has perplexed me. My spreadsheet is set up as a simple checkbook-style registry, with columns for expenses, income, and balance. This, obviously, is simple math.
But I also use SUMIF formulas to track my expenses from this checkbook registry. Some transactions fit into multiple tracking categories, so they take up multiple lines in the checkbook registry. I've uploaded a file with a stripped-down version of my checking registry and highlighted a few examples of these.
What I'm looking for is a formula or conditional formatting in the balance column that will return only one balance for these transactions that take up multiple categories/lines. For example, looking at the very first transaction for mortgage, it is one transaction with a total expense of $1,200 where the new balance should be $1,300. But instead the balance column delivers a balance for each line.
I'm trying to find a solution that:
1. Will accurately reflect my balance at any given time - so I could do a MAX or MIN balance search, for example, and only pull in figures that represent the totality of each transaction and not any balance that comes from one category/line of a multi-line transaction.
2. I can easily fill down in the balance column. I'm often cutting/pasting lines and thus have to re-fill the balance column to keep it accurate.
I've tried merging cells on these multi-line transactions but that makes it very difficult to fill down easily. I tried a convoluted solution with conditional formatting, but that pulls in MAX/MIN results that aren't accurate (like I mentioned in #1 above).
I'm open to any suggestions you might have...even if they're completely different from what I'm proposing here. Ask any clarifying questions you want! Thanks for any help you can offer.
But I also use SUMIF formulas to track my expenses from this checkbook registry. Some transactions fit into multiple tracking categories, so they take up multiple lines in the checkbook registry. I've uploaded a file with a stripped-down version of my checking registry and highlighted a few examples of these.
What I'm looking for is a formula or conditional formatting in the balance column that will return only one balance for these transactions that take up multiple categories/lines. For example, looking at the very first transaction for mortgage, it is one transaction with a total expense of $1,200 where the new balance should be $1,300. But instead the balance column delivers a balance for each line.
I'm trying to find a solution that:
1. Will accurately reflect my balance at any given time - so I could do a MAX or MIN balance search, for example, and only pull in figures that represent the totality of each transaction and not any balance that comes from one category/line of a multi-line transaction.
2. I can easily fill down in the balance column. I'm often cutting/pasting lines and thus have to re-fill the balance column to keep it accurate.
I've tried merging cells on these multi-line transactions but that makes it very difficult to fill down easily. I tried a convoluted solution with conditional formatting, but that pulls in MAX/MIN results that aren't accurate (like I mentioned in #1 above).
I'm open to any suggestions you might have...even if they're completely different from what I'm proposing here. Ask any clarifying questions you want! Thanks for any help you can offer.
