Data validation formula changes on copying sheet

Excel Gurus,

I have run into a very strange problem with excel. I am trying to create an MIS sheet to record the personal financial transactions. There is a "Settings" tab which has the list of categories and sub-categories for the type of incoming & outgoing transactions. In the "Settings" tab itself, I then sort them alphabetically, and then use those lists as drop down menus in "Bank Account" sheets using data validation.

Now, the strange part is that, whenever I duplicate the "Bank Account" sheet, the data validation changes in the Sub-categories columns (Col G). The formula remains the same, but the list names used in the formula gets replaced with other list names !!! In the attached file, see the difference in the "Sample Bank Account" and "Sample Bank Account(2)" sheets. Both are duplicate, but the data validation is different

I have no idea why is this happening. Your help is appreciated.

Thanks

I agree that it is very complex, and it is "humanly" not possible, but once the "Bank Account" sheet is made the right way, excel (which is a machine, not human) should be able to copy the DV as it is when I just copy the sheet.

The difference in the list names in the two DV lists is generated by excel, not by me. You can try copying the "Bank Account" sheet and see the magic for yourself

Both the new copied sheets in the file you attached have different DV list in the G-col. The formula is same as original, but the list names have been changed. Can you please check the G-col of your file again.

I have tried this with 2007 and 2010 as well. Both give the same problem.

Even I realized that copying again solves the problem. But that is not the solution

The real question is "WHY" is this happening ??

I tried changing the names of the lists. Nothing works