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