Excel Formula

[Chris]

I'm trying to do a projection for A/P with a data sheet that has all open
invoices (Vendor, Due Date and Trx Amount), then I have another sheet has
Vendor, amount by current, 31-60, 61-90, and 91+ that list each week for
several months after that.

What I need to do Is compare Column A(Vendor) in both sheets by Vendor
because the data sheet will be different when I run the smart list by new
invoices that are added and then by due dates so that it returns the $ amount
for each invoice in the appropriate column by due date. I'm also going to
need to do a <= and/or >= to capture all due dates. For instance my first
date is 5/9/07 I will need to capture 5/9/07 and anything less then that
date. Also my next date 5/16/07 so I will need to capture anything greater
than 5/9/07, but less than 5/16/07.

Do I need to do a Vlookup and then an If statement? I've been playing around
with it and just can't seem to get it to work.

Thanks

 

[George Nicholson]

I'm thinking Vlookup and a pivot table.

Create a table with your Due dates, call it ForecastDates:

1/1/07 5/9/07
5/10/07 5/16/07
5/17/07 5/23/07
5/24/07 5/30/07
5/31/07 Not Due

(using formulas, you can set this up so that changing one date will cascade
a change to all dates in the table)

then create a helper column with a vlookup formula:
= vlookup(DueDate, ForecastDates, 2)
dates between 1/1 and 5/9 will have 5/9/07 returned, etc.

Then build a pivot table off of that, using your helper column in the column
area.

BTW, your aging sheet could be done the same way:

A table called AgingTable:

0 Current
31 31-60
61 61-90
91 91 +

In a helper column:
= if(DueDate>ReportDate,0,Vlookup(DueDate-ReportDate,AgingTable,2)

Create a pivot table using the Helper column in the Column area.