Excel for EH&S Specialists: How to build a simple manifest tracking spreadsheet
Posted on Fri, May 21, 2010 @ 01:00 AM
By Courtney Drayer, Technical Services Representative
Keeping track of manifest return copies can be a hassle especially if you ship waste frequently. If you have not received a signed return copy of your manifest by 35 days from the time of shipment (per 40 CFR part 262.42), it is time to call your friendly TSR to request it. You can track when it is time to call your TSR by using the following four column spreadsheet:
The magic happens in the status column. Logically, what we want to know is "IF the manifest is older than 35 days AND I have not received a return copy THEN I should call my Technical Services Representative to ask for it." You also want to know that "IF the manifest is older than 35 days AND I have received a return copy THEN I am in compliance." And finally you'll want to know "IF the manifest is younger than 35 days AND I have received my return copy or I have not received my return copy THEN I am in compliance". We can say all that in excel using the following formula in column D;
=IF(AND((TODAY()-B2>35), C2="N"), "Call your TSR", "Compliant")
Now all you have to do is enter your manifest #, the shipment date, and whether or not you have your return copy and the spreadsheet will tell you the rest! Since we have used the "TODAY()" function in the formula, the spreadsheet will automatically update every time you open the file so you don't need to keep track of the dates anymore!
If all this still sounds like more data entry than you have time for, you can use Triumvirate's ADVISE software to track manifest compliance.
Your manifests will automatically appear in
ADVISE with red, yellow and green dots showing you the compliance status of each one. You can also scan in and upload your manifest copies (generator’s copy and returned copy) and save them in the program.
ADVISE can be accessed from anywhere you have an internet connection.
So, if an inspector shows up for a paperwork review at one of your sites, you can show them all of your manifests from all of your sites from the comfort of your desk!
Now that’s easy.