Please bear with me while I provide a little background information. I became the back-up to our Excel Guru at work, so I've learned a lot from her. Because the company I work for is involved with numerous projects (currently 100+), my team of 60 is required to keep track of how many hours they've spent working on the projects they're involved in. Thus, I have more than 60 excel workbooks that I'm now solely responsible for as our Guru has left the company.
Each employee has a workbook set up where they record their time. It's quite extensive, really. I have attached a copy of a blank version of the employee workbook and the master list.
Currently, for collecting the data we need to report to upper management, each employee completes their time sheets (the blank workbook). This data is feeding into reports that are compiled daily, weekly, monthly, and yearly. With the new year right around the corner, I had to pretty much recreate the workbook that you see if you've downloaded the attached files. Don't ask. Let's just say the original file got all sorts of corrupted and it's not good.
But - in the original files, our Guru was able to use the following in the Data Validation for a list: =ProjectList!$A$3:$A$100 - This allowed an employee to select the projects they're working on so they could enter the time worked in their timesheet under the Project Run section of their workbook. (Screen snippet shown below) The drop down is pulling the info from the Master List.
Capture.PNG
I don't know what she did in order to use that reference in the Data Validation because when I do it, I get yelled at by Excel telling me I can't use a reference to another workbook in Data Validation. Does anyone know how I can get this to work like it use to? I don't have the contact info for the person who left and no one else I work with is all that good with excel.
I have searched online but anything that I have found and tried hasn't worked.
Thanks in advance for your help
Bookmarks