Excel Formula
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 |
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 |
All times are GMT +1. The time now is 10:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com