Budget Sheet, date ranges, moving targets...

[Unknownforce]

I have a pretty basic Budget sheet setup... (Basic to the eye)

Dates down the left side, followed by income, followed by bills, credit card payments, food, gas, etc. etc.

Ending in Totals, total charges this week, Total in relation to how much I have Left over each week compared to that weeks income, total after all is said and done and then add to previous weeks total down the list, and then finally a total increase for each month... to make sure I'm staying positive...

It's pretty basic... add it all up, subtract what's getting charged, then figure the totals...

I'm working on automating the main Budget Sheet based off of values entered in other Sheets (a Sheet for Bills, a sheet for Credit Card Payments, etc.)

I got the bills part down because they fall on a specific date each month and the charges I always estimate or know the exact total that's going to be charged every month, so I use a series of IF's, DATE's and VLOOKUP's wrapped in an IFERROR and compare date ranges and yadda yadda to display an amount based on whether or not the bill will be hitting in the week within the Friday date listed at the beginning of that row. ( I get paid on Fridays. )

What I'm running into now is my Credit Cards... because the payments vary month to month, I'm forced to enter those in somewhat manually, which okay that's fine, but I'd like to just update a Sheet, and let the budget sheet update based on the credit cards sheet...

What I would like to do is simply enter the date of the payment, the charge of the payment and then do a vlookup based on the name of the card...

So I have a table, with the following headers: CCName, CCPayment, CCDate. All pretty obvious what I enter in for data. What I wanna do is enter in multiple payments and dates for the same CC name, and then have each CCPayment show up in the budget sheet in the proper column (Same as "CCName") and Row, but also based on the date range This Friday >= CCDate > This Friday +7, so that I know that it falls within the week specified. What the obvious flaw here is that the CCName isn't unique, so a VLOOKUP will only pull the first one...

I'm not entirely familiar with INDEX and MATCH... and I've done my fair share of googling... but I'm not sure exactly what I'm missing here... a VLOOKUP nest?

There has to be a way to do this based on the month that the Friday date falls in and compare it to the Month that CCDate falls in to pull that CCPayment...

As an example here...

Date	CC1	CC2	CC3	CC4
5/1			60
5/8		75
5/15	102
5/22				200
5/29			90
6/5		75

CCName	CCPayment	CCDate
CC1	102	5/17
CC2	75	5/9
CC2	75	6/8
CC3	60	5/3
CC3	90	6/1
CC4	200	5/26

Any help would be greatly appreciated. Thanks!

[Toonies]

it would be best to upload an example sheet

[samba_ravi]

see the attached file

[Unknownforce]

Wow thanks for that. But unfortunately, it's backwards, I want the top areas to populate based on the bottom info, not the other way around.

Thanks for the example though, it's pretty complex and good info for other possibilities here.

[Unknownforce]

Here's an example of what I'm trying to accomplish... Trying to populate the right table with the data from the left...

I've gotten them to populate in RED, but the ones in Green don't. I know why they don't, because INDEX/MATCH will only pull the first match, but I don't know how to pull the right ones here...

Example

BTW, The document linked is editable.

[Unknownforce]

Please Login or Register  to view this content.

Bump, there's gotta be a way to do this... I've been trying new forumlas over and over, and not getting any success here... My brain is fried...

Funny thing is, I'm probably missing something stupidly simple, like I always am!

[Unknownforce]

Finally found what I needed for this... In case anyone cares...

My formula came out to =IFERROR(VLOOKUP(G$1,IF(($C$2:$C$100>=$F2)*($C$2:$C$100<$F2+7),$A$2:$C$100,""),2,FALSE),"")

I updated my Example sheet with it.