I've been tasked with reorganizing our division's record management system. Right now we have a thousand different tracking systems, none of which have been consistently or reliably used, so I'm going to start a new one that will form the department's tracking spreadsheet. However, I've run into a bit of an issue.
Currently I'm listing the record and then using a drop-down list in column B to determine the type of record and a corresponding vlookup to determine retention period (listed in a seperate sheet) in column K. Then the file date is listed in column L, and column N takes the file date + retention years + 1 (in order to ensure that we're keeping records for the minimum amount of time required), giving the year it can be disposed of.
However, this only works for boxes with single records in them. We have a few boxes that store multiple entries that may have different retention periods. For example, if you look at rows 98-99, both items are in the same box but both have different retention periods. I want the formula in column M to automatically indicate the highest retention period--so, in 98-99, it would be 2002+10+1, for 2013, rather than 2003+6+1 for 2010. I want the formula to list this date for both items, regardless of what item is in them, so that we're sure not to destroy the box.
As you can see from the formula in M98, right now I've got the formula set up to reference the "multiple entries per box" column, which uses a countif formula to determine if the box number is repeated at any time. If the box number isn't repeated it spits out the logical retention date; but if it is repeated, I've set it to give nothing. I can't seem to figure out the best way go about this. Any help would be greatly appreciated!!
Currently I'm listing the record and then using a drop-down list in column B to determine the type of record and a corresponding vlookup to determine retention period (listed in a seperate sheet) in column K. Then the file date is listed in column L, and column N takes the file date + retention years + 1 (in order to ensure that we're keeping records for the minimum amount of time required), giving the year it can be disposed of.
However, this only works for boxes with single records in them. We have a few boxes that store multiple entries that may have different retention periods. For example, if you look at rows 98-99, both items are in the same box but both have different retention periods. I want the formula in column M to automatically indicate the highest retention period--so, in 98-99, it would be 2002+10+1, for 2013, rather than 2003+6+1 for 2010. I want the formula to list this date for both items, regardless of what item is in them, so that we're sure not to destroy the box.
As you can see from the formula in M98, right now I've got the formula set up to reference the "multiple entries per box" column, which uses a countif formula to determine if the box number is repeated at any time. If the box number isn't repeated it spits out the logical retention date; but if it is repeated, I've set it to give nothing. I can't seem to figure out the best way go about this. Any help would be greatly appreciated!!
