'grabbing' a sum from a separate table

[AbbeyMetal]

Im new to excel and i am wanting to have two different tables. one with my bank balances and the total for each day and in the other i have my bills. im wanting to see how much i have available on each day without having to go back and forth between tables. i can manually plug in a formula that grabs the number specific to the day im on but i want it to be autonomous. every day i check my bank accounts to see how much i have available and i enter the values daily. i want excel to read 'if theres a bill with a date the same as the statement, then display the bank balance in this row, else 0.' any ideas on making this work? Thanks

[gebobs]

Let me see if I understand...

One table for balances. For simplicity's sake, just one account for now, with consecutive dates (say for August 2014 for now) and the balance for each date.

Second table is for bills: payee, due date, amount.

For each bill on this table, you want to display the balance for the date that equals the due date. Is that right?

[AbbeyMetal]

yes that is what i think im trying to say. i just want the bank amount to be displayed to have a side by side comparison on how well im keeping on track with my bills as to the bank says.`

[gebobs]

See if this is along the lines and we can go from there. I gave you some nice balances. ;-)

[AbbeyMetal]

would that work if i have many bills on the same day? meaning i have 10+ bills on the 1st of the month.

[gebobs]

It will post the same balance for each.

[AbbeyMetal]

what does it mean when you have the A#:B#? i cant figure out how to refine the reference to that particular day. everything im trying isnt working.

[AbbeyMetal]

well i think i got it working but when i ctrlD the function it works for the first few, up until the A# gets out of range of 8/1..is there a way to copy a formula all the way down to read just the sum?

[AbbeyMetal]

this is what i am talking about..if it helps

[gebobs]

Good grief, my bad. Simple error. We need to use absolute references for the lookup table.
The current equation in D2...

=VLOOKUP(B2,Balances!A2:B21,2,FALSE)

Excel automatically increments references so the lookup table then is A3:B22 for the equation
in the next row, A4:B23 after that, etc. Making the reference absolute, the equation becomes...

=VLOOKUP(B2,Balances!$A$2:$B$21,2,FALSE)

What this does is tell Excel to not increment either the row or column reference. Since we are
copying down, all we really care about is the row reference so we could just put the $ in front
of that part: A$2:B$21. Or, more simply...

=VLOOKUP(B2,Balances!A:B,2,FALSE)

This makes the entire columns A and B the lookup reference and thus leaves room for you to
add rows nearly indefinitely (over one million rows in Excel 2010).

Also, we can add error trapping (use this equation)...

=IFERROR(VLOOKUP(B2,Balances!A:B,2,FALSE),"")

In summary, what this says is:

* If there's any error in the equation (e.g. if there's not balance date found that matches the bill date),
then just leave a blank space, "".
* Otherwise, take the bill date.
* Look for it in the Balances table.
* When you find it, get the balance in the 2nd column.
* It must be an exact match (the FALSE specifies this).

[AbbeyMetal]

and if theres no data for the blank would i still be able to take the sum of the column? because i know in access it can be a little touchy on that type of thing.

[AbbeyMetal]

I got it to work!! thank you so much for your help!!you were fantastic!! 2 thumbs way up

[gebobs]

Grassy ass!!