Transfer of data from input sheet to one of three data sheets

[Hirad001]

Hello All,

I have a workbook that I am trying to develop to collect information regarding time spent in various activities e.g. A, b, c, etc. At present all this information in manually collected for each employee over 150, this is then put into a workbook and create useable management data. As this is taking me a considerable amount of time I am trying to improve the efficiency of the process. I have created a test workbook to work on this, firstly creating a dependant drop down to access employee.

I envisage entering the data each day into the input sheet & was hoping that this data could be transferred to the appropriate data storage sheet through an automated process, that would also clear the input data that has been transferred, leaving the input sheet ready to use again

From the data storage sheets, I can them pivot the data to obtain information required and graphs etc., as of which I can do

I would appreciate any help/guidance you could provide. I attach sheet to show what I an trying to do

Many thanks
Mark

[AndyLitch]

activity measurement.xlsm

Hows that?

[Hirad001]

data transfer 1.xlsmAndy

Many thanks that is exactly what I was looking for. Absolutely brilliant.

Having played with the data transfer, I have noticed that I am able to transfer data across without a date. Is it possible to prevent transfer until the date field C1 is populated with a date in the format dd:mm:yyyy

I had intended to use a drop in calendar, which I found at https://sites.google.com/site/e90e50...ker-Popup-Form

I have already copied the Class Modules; cCalendar & the form; DatePickerForm to achieve this into the attached file, but as yet been unable to link calendar to cmd button as per your data transfer button.


The second issue is that of preventing employee data being entered twice for the same day. I am going to look at altering the drop down to only display the names that haven’t already been used, however it has occurred to me that if the data was posted on several separate occasions it could still lead to duplication of information. Would it be possible to inbuild a validation check that searches the relevant data store sheets & only if there no entry for that staff member already present for that day would the transfer include the data, if the data already existed for that employee for that day that line would be excluded?

Many thanks for what you have already done, and hope you will look kindly on my questions

Many thanks

Mark

[AndyLitch]

activity measurement.xlsm

Try that for a date fix.. It won't tansfer data without a date and when you select cell C1 a userform datepicker allows you to select the date.

Checking for duplicate entries is a bit more tricky and depends on what you want... Presumably you want to check and eliminate duplicates on the input sheet but which one should be deleted ?

[Hirad001]

Andy

The date fix is just great

Many thanks

[Hirad001]

Andy

As data could be entered on several separate occasions and therefore update to the datastores several times, I would want to test if there was a record that already sat in the datastores and if so dump the current data transfer line (potential duplicate/s) to a dump sheet so it could be manually validated.

If all data was entered at same time the Hide Used Items in Drop Down List would prevent duplication, though still to sort this out

What do you think?

Thanks Mark

[AndyLitch]

activity measurement.xlsm

And there's a basic system to prevent duplicate entries

[Hirad001]

Andy

just brilliant

Many thanks
Mark

[AndyLitch]

Glad to help Mark