Need formula for auto filling text next to dates.

[troubledXL]

Hello forum,

I have been struggling with this question for a long time. I'm hoping you can help! In my family budget (scrubbed sample attached) SampleBudget.xlsx I have a list of expenses, the day of the month they automatically withdraw from my bank account, and the amount of the withdrawal. I like to forecast out my auto withdrawals for the whole year, and have been looking for a formula that will look through the column containing a date for every day of the year, and then say place the expense in the line next that date. For example. My car loan comes out on the 1st of every month. I would like the formula to place the expense "Car Loan" next to every 1st of every month (4/1/15, 5/1/15...etc) as well as the amount.

My ultimate goal is to be able to:

1. Be able to add expenses to my Expense Table and have them populate throughout the year.
2. Be able to change withdrawal amounts in my Expense Table and them populate.
3. Be able to change withdrawal days, and have the expense date move.

This saves a LOT of copying and pasting, and my ultimate goal is to be able to create a dashboard tab where I can change multiple variables, and then jump over to my Budget Forecast and see how those variables affect my income/expenses.

Last note: I think the biggest hurdle is when I have two expenses coming out on the same day. I doubt there's a formula that says "When there are two expenses on the same day, add a row and insert the second expense on the new line."

Thank you in advance for your help on this! Although my uploaded example looks simple, my actual budget is very complex and I have been using it for several years. Constantly copying and pasting test scenarios (what does it do to my budget if I increase my student loan bill $20/month?)is the single biggest consumer of my time in all my family finances!

[soberguy]

In C, assuming you only have 2 items that will ever be due on the same day:
=IFERROR(IF(COUNTIF($F$2:$F$13,DAY(B2))>1,CONCATENATE(INDEX($F$2:$G$13,SMALL(IF($F$2:$F$13=DAY(B2),ROW($F$2:$F$13)-1),1),2),", ",INDEX($F$2:$G$13,SMALL(IF($F$2:$F$13=DAY(B2),ROW($F$2:$F$13)-1),2),2)),INDEX($F$2:$G$13,MATCH(DAY(B2),$F$2:$F$13,0),2)),"")

Enter that with Control+Shift+Enter as it is an array formula.

In D:
=SUMIF($F$2:$F$13,DAY(B2),$H$2:$H$13)

[alansidman]

See if this is helpful:

Please Login or Register  to view this content.

[troubledXL]

Thank you SO MUCH! This worked perfectly, exactly what I needed. I can't wait to get home and stick it in my budget! I'm flattered that my question produced such beefy looking formula, and impressed at how quickly you did that. Thanks!