How to reference 100's of items with corresponding triggers to designate an end date.

Sarah K. Steele

Hi -

 

Our agency has a formal record retention policy that lists over 100 types of records that we store or have digitized in-house.  

  

Question to answer/example:  If the documents for Site A fall under Record category 1, they  should be destroyed on 1/1/11, which is 100 days following the trigger date.

 

 

I was able to create the formula that completes the equation and creates the date of destruction on my retention policy sheet, but I need to refer to that formula when a document is added in one of the many sheets that list all of our individual documents.  

                      

How do I reference a corresponding set of cells listed in my main sheet automatically, when I assign the record type to a document  in a separate sheet?

 

In this case I have a form that the staff member fills out and identifies the record type.  The new document is populated onto an inventory sheet.  At that moment, the document should be automatically assigned a disposal date based on the retention policy.  

 

Thanks for your help.

 

Sarah

Data Solution Consultant

Hi Sarah,

 

I am just putting data sample based upon your description.

Please let me know if it is in the right direction.

 

Document         Created        Category            Retention Date

 

doc1                  9/16/2015        A                       10/16/2015

doc2                  9/16/2015        B                      11/15/2015

doc3                  9/16/2015        C                       12/15/2015

doc4                  9/16/2015        D                       10/01/2015

 

Assumptions :

If category = A add 30 days

If category = B add 60 days

If category = C add 90 days

otherwise

add 15 days

Brett Evans

DisposalDate = CreationDate + IF(CategoryColumn = Category1, 100, IF(CategoryColumn = Category2, 200, IF(CategoryColumn = Category3, 300, IF(CategoryColumn = Category4, 400, 1000))))

 

Your disposal date formula would look something like the formula above.  In english the formula is saying: the disposal date is the creation date + 100 days for a category 1 doc, plus 200 for a category 2 doc, plus 300 for a category 3 doc, plus 4oo for a category 4 doc.  For anyother category add 1000 days.