On 'Closed' sheet (assuming headers in Row 1) ---
in D2 enter
=VLOOKUP(A2,'0100 RETENTION (2)'!A:C,3,TRUE)
to get the 'Destroy' date of the order in A2
in E2 enter
=DATE(YEAR(C2)+10,MONTH(C2),DAY(C2))
to add 10 years to the 'Close' date
in F2 enter
=IF(D2<E2,"Destroy date is less than close date+10 years","Ok to destroy")
---------------------------------------------------------------------------------------------
You can also
in G2 enter
=VLOOKUP(A2,'0100 RETENTION (2)'!A:D,4,TRUE)
to get the 'Lot#' after entering sequence number in Col D of '0100 RETENTION
(2)'
enter in E2 of '0100 RETENTION (2)'
=SUMPRODUCT(--(Sheet2!$D$2:$D$11<Sheet2!E2),--(Sheet2!$F$2:$F$11='0100
RETENTION (2)'!D2)) and copy down
to get the no of orders in each 'Lot' which should NOT be destroyed...


--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"dan dungan" wrote:

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