Excel 2007 : Drop down List problem!

Hi new member here, but im getting a reasonable grasp of excel by now. Mainly due to work purposes, its great but the more advanced i get the more complicated the problems so please bare with me and ill fill you in.

SO i set up timesheets for everyone in the office you have a tab for each week of the month. You enter the jobs you work on and the job number on the first tab (we will call it the "job details" tab) then on all the weekly tabs you have drop down lists referencing this "jobdetails" tab where it pulls in that job name. The idea being if you work on the same job week after week you enter it once on the first tab and from there on in you just click the drop down and it enters all the details for you.

Everything was going fine, the office have had the programme with no issues for a month or two. But now some of the users are starting to have problems all of a sudden. For a few users the drop down boxes still show up but when you click on them; excel thinks for two seconds and then nothing happens, there are 6 drop downs on each tab and it happens across the whole workbook.

So i looked at the source that the drop down list is referencing and for some reason its changed from the "job detail"! $a$16:$a$83(which was working fine) to the whole file path so P:documents,jobs,timesheets,JoeBloggs...etc. I dont know why this is happening can anyone help?

NOTE: I have no macros operating on the spreadsheets but i have got all macros enabled in the trust centre as i was getting errors a while back. Also all workbooks are saved as macro enabled worksheets. If needs be i will explain why.

Let me know what you gurus think & thank you in advance for your help.

It would be easier if you could post a sample of the worksheet that you are having problems with. Just change any sensitive information to something random

sounds like you used the cut and paste and the formula got messed whit, aka you probebly copyed it to another sheet to use :P so you cut pasted it :P im guessing but sounds like one of those types of problems.

ill will wait for it to happen again and ill post the file. fingers crossed i guess.

Time Sheet.xlsx

As requested please find the attachment showing my issue. Cell C11 has the problem and cell C12 is an example of a cell thats fine.

I think its because in the process of creating the file i messed around with macros trying to get them to function as part of the system. They didnt work so i am not using them but i had to mess with security setting; so i think that might be something to do with it im not sure.

Also some of the drop down lists are now completely disappearing so i open it and go to conditional formatting and its as if i never created a drop down it has no memory. As its all to do with the drop downs i reckonthis problem is tied to the first.

If anyone can help me with either one of these problems that would be great.

It would be easier if you could post a sample of the worksheet that you are having problems with. Just change any sensitive information to something random

I have posted the example below for you. Have a look and tell me what you think. Any feedback would be appreciated. thanks!