Array formulas not working after close/re-open

Hi Everyone,

I am going to try and put as much info here as possible for you all to work with. I have created a workbook for a large group of technicians to use for data entry. It'll save me hundreds of hours if I can get it to work properly. I've tried to set it up to be as fool proof as possible. Some of the guys I'm dealing with aren't the brightest. Sheet 1 is the only sheet they should interact with and its locked except for the few cells they need access to. Sheet 2 simply gathers info from sheet 1 in a way that is easier for me to work with later. Sheet 3 (which I keep locked and hidden has my lists for data verification).

It's fairly cut and dry I think. It's all built and saved with Excel 2007. I have a macro running that forces letters to turn to caps upon entering in the "code" input cells. I can't trust everyone to turn or leave caps lock on. In those same cells, I have a custom data verification formula that is meant to 1) verify off the corresponding list, and 2) check and prevent the corresponding range of cells for duplicate entries. I am absolutely sure to commit the formula with ctrl+shift+enter. Everything works great until..... Once I've tested it and locked it all up, I save it as a .xltm template. I want to do this so each technician can just open it up and it be ready to go for their input. Then they just save it (as .xlsm by default) and leave. Unfortunately, it's not working. When you go to open the template, everything looks fine but the data verification formula in the "codes" cells doesn't work all of a sudden. If I open up the data verification dialog, check the "apply to all similar" box, and then re-commit the formula with ctrl+shift+enter, it'll start working again. I don't even have to change the formula at all. Just re-commit it. WHY?? Of course I can't expect the techs to do all that every time they open the template to input their codes. Why isn't the formula functionality working after I close and re-open?

I can't think of anything that I'm missing. I need help. Macros are enabled and calculations are automatic. I've poured over this thing and can't figure it out. I have attached the file to this post. The site won't let me upload it in .xltm format so I had to upload it in .xlsm format. Try saving it as a .xltm template and then re-opening it. If the same thing happens to those of you who take a stab at it, then you'll have to recommit the data verification formula in both of the codes sections with ctrl+shift+enter in order to get full functionality.

Feel free to tear it apart and let me know if you solve it or have any ideas. Don't forget to unhide sheet 3 if you want to see it and the password to unlock all three sheets is "dailysummary". I am very much looking forward to any helpful responses. Thanks.

Try this for the Data Validation formula (in J8 and similar cells):

=AND(COUNTIF($J8:$O8,J8)<2,COUNTIF(newcodes,J8))

That worked perfectly!! Thank you! Do you have an explanation as to why it wasn't working before? It would be nice to know just for my own info.