You will face the above stated issue if the Excel sheet is created in newer version of office like 2010 or 2013. These two versions support to create a drop down list with data from another sheet but all the previous versions can only select the data for the drop down list from the same excel sheet. if you are facing the above problem with your drop down list copy the data to the same list where you are creating the drop down list and recreate the drop down menu.
Well, this is problem faced in many cases . commonly this is faced when the Excel is created in any other version and later it is opened in another version
Thanks for the solution.