Data Validation (drop downs) issue - suddenly no data available

Dear Reader

i have a excel file with a couple of sheets. on every sheet i have 2 drop downs (data validations) and suddenly the data is empty.
every sheet is protected and without changing something on this sheet or on the data linked in those drop downs - those drop downs are suddently empty.

i attached two pictures 1 with working drop downs and 1 with suddenly not working drop downs.
before "releasing" a new revision of this excel file i always check the drop downs, protect the sheets and save the file - but still suddenly a couple of drop downs doesnt work anymore..

working drop downs.png working
formula in the drop down (data validation):

Please Login or Register  to view this content.

not working drop downs.png not working
code of the not working data validation:

Please Login or Register  to view this content.

suddenly the sheetname disappears... or there is a error message (can't remember what it says) and the whole formula is disappeared.

does anyone know what the issue could be? slowly but sure it is become a never ending story of correcting (copying from working sheets) the not working drop downs, checking if it works - it does after copying.
i hope you understand what i am trying to explain otherwise sorry for my english skills (swiss german is my mother tongue).

what i would like to know is:
is this a user (myself) issue and how can i avoid this problems or when it is an excel issue, is there a work around, bug fix oder anything to avoid this?
i am using for developing excel 2013 but my fellow coworkers are using excel 2010 (sheets are protected) they can only input values in marked cells or select data from the drop downs otherwise they are not able to overwrite or change stuff.

i am looking forward for your help.
best regards
blacky

no one an idea?

Really difficult to tell what the issue is without a sample spreadsheet, but I would guess the sheet name is disappearing just because the formula is in the sheet of the same name so it's unnecessary?

But that said, one way around the issue would be to use named ranges - highlight, for instance StressTableASME!$A$50:$A$100, then give it a sensible name in the cell ref/name box under the ribbon. You can then refer to the names rather than cells, which should lead to more stability:

=IF($D14="EN";OFFSET(ENoffset;;;COUNTIF(ENStressTable;"*?"));OFFSET(ASMEoffset;;;COUNTIF(ASMEStressTable;"*?")))

thank you, i will try with the "names". the Enstresstable & amsestresstable are tables with numbers and every working sheets is taking the "materialnames" from the stresstables.