Trying to figure out some budget calculations and formatting?

[stebesplace]

I have attached an example, as well included this screenshot to show you what I am talking about below.



This is what’s going on here; On the red line, this is a normal transaction where I enter in a value, select my category, and the correct category fills in that value. There are 2 categories here, so if I selected ATM Cash in Column 2, it would have put -5.00 in that column and 0 in the Purchases column.

Now, in the event of a category adjustment as a line item, as outlined by the row in blue, I have entered a category in column 2 which is my from category, entered my to category in column 3, and put in my positive dollar amount to adjust in column 4. What should happen then is that column 5 and column 6 show the correct added and subtracted value amounts as a “From/To” type of display.

The catch here is that as more rows of entries are put on the sheet, there are no defined rows for category adjustments, meaning that column 5 and 6 need to have a formula that can accommodate for both situations listed above. These two situations are (Normal Entry, and Category Adjustment).

Hopefully I was able to explain this well enough. I am at a loss and have tried nested if statements, etc. If anyone has a clue, let me know.

[Excelenator]

The formula for Column F is

=IF(AND($D2=0,$B2=F$1),$A2,IF($B2=F$1,$D2,-$D2))

You can copy this down as far as needed in column F and over to Columns G through however many columns you have.