Reply to thread

Message: [QUOTE="dan dungan, post: 3789192"] Hi, We have storage boxes that have a destroy date. The boxes hold 100 sales orders. Each sales order has a date closed. The box is scheduled to be destroyed 10 years after the close date of the 100th order number. This was not a good strategy because some of the orders closed after the 100th order in the box. So we may be destroying documents that should not be. I need help with a formula or vba to find out if an order's closed date is 10 years before the storage boxes destroy date I tried the following formula, but it doesn't work because I'm not determining the between order number part correctly. The details are described below my failed formula. =IF(AND(A23387,'0100 RETENTION (2)'!$I$119,'0100 RETENTION (2)'!$J $119,VLOOKUP(VALUE(A23387),'0100 RETENTION (2)'!$I$119:$K $262,3,FALSE)),IF(AND(A23387,'0100 RETENTION (2)'!$I$118,'0100 RETENTION (2)'!$J$118),VLOOKUP(VALUE(A23387),'0100 RETENTION (2)'!$I $119:$K$262,3,FALSE))) I appreciate your feedback. Thanks, Dan ____________________________________________________ We have a storage log named "0100 RETENTION (2)" that shows the description and the order numbers in the box, like this: Begin End Order Order Number Number Destroy Date 60300 60399 Dec-2013 60900 60999 Jan-2017 61100 61144 May-2013 61200 61399 May-2013 61400 61499 Dec-2013 61500 61599 Jan-2014 61600 61699 Oct-2013 61700 61920 Dec-2013 62000 62199 Sep-2013 62300 62324 Jun-2014 We have a report from the system in another sheet named "closed" that looks like: Order # Status Close Date 62300 C 02/27/03 62325 C 02/28/03 62500 C 04/01/03 62600 C 03/07/03 62900 C 04/03/03 63000 C 03/28/03 63100 C 06/03/03 63200 C 04/07/03 [/QUOTE]

Verification