I frankly suggest that you (and your workgroup ...) are using altogether the wrong approach.   You are using Microsoft Excel spreadsheets as a database.   Why not use a database ... within Excel?

Not “using an Excel spreadsheet as a database” (which it’s not, but, “using an actual database (of your choice), within, and by, Excel.   Excel has many powerful but seldom-used capabilities for accessing external data as live views.

See for example this article.

The task that you describe appears to be a classic database task ... made even weaker by the fact that you are this application-design appears (as you describe it here) to be deleting information from the workbook.   Information that, of all rights, should be important and valuable to the business.   You would want a completed test to disappear from view, because it will no longer be SELECTed, but not to disappear altogether.   Furthermore, these data should be stored in a venue considerably more robust than “a file that is constantly being destroyed and replaced.”

If you do have to continue this task, in this way, then let me please be quite direct and say that I think you should be doing it in VBA, within Excel.   Or, at the very least, that you (I will say ...) must be doing it using OLE.   There’s a reason for that:   in both cases, “Excel is the one who’s actually doing it.”   Therefore, also doing all of the calculations that are called-for and so forth.

Maybe not the politically correct thing to say on a “Perl” forum, but in the interest of helping to sketch out what (I think) is the best strategy:   “this strategy is extremely common, but, ‘this isn’t it.’ ”   If you have the power and the standing to suggest that Excel could do this job better and differently with another approach being taken, I would suggest doing so.