Excel Formula
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(DueDateReportDate,0,Vlookup(DueDate-ReportDate,AgingTable,2)
Create a pivot table using the Helper column in the Column area.
"Chris" wrote in message
...
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
--
Chris Jones
|