Data Validation source from a separate Master list workbook

Are you open to another means of tracking? Of all the disciplines that use Excel, it seems HR manages to produce the worst spreadsheets especially for time management. There are 12 separate sheets: one for each month. And there is 28 to 31 columns across to represent the days in the month. And then they wonder why, with data scattered all over the place, they can't run a pivot table or any other useful report off of this data.

What they have created is what the final report should look like.

On the surface, this looks like a reasonable thing, but it is only reasonable if this is the only way you ever want to look at the data. If you can enter the data in a normalized format then you can present it like this and many other ways as well. Reports that span, months, contractors and projects are possible. What you want to do is better done by a database. However with a little programming, for simple tables, Excel can be made to act like a database.

No matter which course you choose, getting a common source for every sheet, that could be done but you will need a directory that everyone can access to contain a file that has the official project names and then import them into the individual sheets.

I can set up the lookup rather easily. If you want the normalized system, it will take some development.

I'm working on a time card system that will work with excel. It will consist of a back end where you tell it what the project names are and who the employees are and the start date of the time period and number of weeks in the time period.

Then it will create one front end time card sheet for each employee and mail it to them. They fill it out and click a button to submit the time card. The information is collected in the back end in a table and this table can run various reports.

The requirement is that each employee have a unique ID and that everybody has access to the file where the back end is kept.

I will attempt to get this set up by the end of this weekend.

A future version will isolate the back end. Clicking the button will send the information to a specific mail address and there will be a routine to download the attachments from this mailbox and process them.

My motivation is that I have been helped by this forum and I need to give back. Also, I just landed a job to be an Excel guru. I've been laid off for several months and I need to get back "in training." So the price is gratis.

I've finished the project except for testing and for some minor tweaks.

While you are waiting for the finished product, I suggest you contact your IT department and have them establish a Common Mailbox to which you have administrative rights - also grant rights to other people who may be processing time cards. This will allow you to take a vacation and your backups can do the job for you.

Here is a high level overview: you have a main file in which you identify employees, identify projects and identify pay periods. For example if you identify the start of a pay period as 1/6/2020 and one week as the duration, it generates one week's worth of input slots. If you designate two weeks, it makes two weeks worth of slots. You can change the employee directory and project list any time.

You click a button and the information is used to update a template file that contains almost all the information needed for an employee to fill out a time card. Then the program copies the template to a file (one per employee) and tweaks the file for that employee and mails the time card to the employee.

The employee fills out the card and clicks on the submit button. This normalizes the data on the card, and mails the card to the common mailbox automatically. The employee does not have to take further action. Mailing is automatic with the submission.

There is another spreadsheet that reads the mailbox, pulls out the attached time card, and archives the email. There is a second button that processes these cards into the common database and archives the source attachments.

Attached is the documentation for the project. It is subject to change as I test and tweak. It will give you more details on how the system will work. Be diligent in setting up the system initially. Once set up, the only maintenance you should have to do is on the employee table, the projects table and reset the date for a new time period when generating time cards. The rest is button pushing.

Employees will have to accept macros to get the cards to work. If they make a folder called Time Cards (or whatever they want), they can make it a trusted location. I can give instructions on that later.

The data is collected into the common database by date. So if you collect it once a week or every other week, you can report it by day, week, month, quarter or whatever period you choose. That's the beauty of normalized data.

Check this out. https://www.excelforum.com/tips-and-...ml#post5252373

It uses an email system, but I went with the most good for the most people, and this seems to fit the bill. If you don't want to set up a common email box using [email protected] will work. But then you'd be the only one who could handle time cards.

Awesome, thanks so much! I'll check it and see if I can pitch this to upper management Wish me luck in that arena!


On the off-chance I cannot get management to go with this, is there a solution to my original dilemma?