Consolidated Workbook

[yus786]

Been using the this timesheet (http://www.excelforum.com/excel-prog...87#post2786087 for weeks and now and sweet as a pie. BUT (Don't you just hate buts), my management now require a change.

Currently we are reporting on the hours worked per project but what they now need is hours worked tasks per project per country.

I have included an example - have a look at NewTimesheets_V1 - EE1.xls and NewTimesheets_V1 - EE2.xls

1.. The days are at the top and the new columns are Country and Tasks. I want the ADD Client button to ADD a new row and DEL Client to DELETE. The submit, i can do
2.. Sheet 2 i have left visible for you to see what's going on, but i can hide that later

Now have a look at the total sheet - TotalV1.xlsx

1.. The first tab needs to have a consolidated total of the lot (like done on the earlier ones)
2.. Then the next few tabs would be a copy of the individual sheets.
3.. And you can then see the pivot at the end.

Is this possible to do?

It's all similar to what was done earlier with the exception of moving the days to the top and adding 2 more categories.

The purpose of this is so that we can track who's doing which project in which country and which task are they actually working on

Appreciate your help

[shekhar1660]

Hi,
Can you help me by explaning the manual steps you want to include in "TotalV1.xlsx"?

[yus786]

Hi,
Can you help me by explaning the manual steps you want to include in "TotalV1.xlsx"?

Yes of cause and thanks for looking at my issue

If you have at look at the 2 individual sheets, they are for 1 person each and they would be saved into a location each period.

The idea is for my manager to go into this folder and open up this TotalV1.xlsx. This file should bring all the individual in and also consolidate them.

So the 1st tab would a total of ALL the individual ones, the other tabs would be the sheets from individual coming in, and then a pivot at the end, where we can filter and play with analysis

Hope this makes sense

Thanks for looking all

[jaslake]

Hi Yousuf

Please post your workbooks with your existing code that you indicate works. We'll try to modify it.

[yus786]

Will do John once in the office. Thanks again

[yus786]

Really really sorry about the delay John. Only just got back today.

Please find attached the 2 timesheets that we use on a daily basis. I have removed the EE and Clients names for data confidentiality

Appreciate your help as always

[jaslake]

Hi Yousuf
What will your Total Worksheet look like in your new TotalV1 file? Mock up a sample based on the files you posted in Post #1.

[yus786]

Hi John, something like the attached? Like a pivot. Where a filter would be required on the Employee at the top.

Don't really know how else to otherwise that tab will be huge with rows and rows of figures.

Let me know what you think

Thanks

[jaslake]

Hi Yousuf

I don't know...have to think on that. Just starting on the rewrite and needed the layout of Total worksheet. Get back to you.

[yus786]

OK thanks John, i'll leave it with you. Appreciate your help once again

[jaslake]

Hi Yousuf

As I mentioned previously this is a major rewrite...attached is the first installment. I need you to tell me if we're on the correct path.
I believe the code does this

Where a filter would be required on the Employee at the top

The Original Pivot Table appears to be in tact and should appear as desired.

Please place ALL the attached files in a SEPARATE folder for testing. All the files should be in the SAME separate folder.

If the code appears to work on the test files then try the code on COPIES of your live files. Let me know of issues.

[yus786]

Hi John, the total tab should be a total of all individual sheets.

But i can see Client 2, in DE doing Task 4 is on there twice. Which is correct but rather than being there twice, could we not have a total of the 2?

Is that possible?

THanks

[jaslake]

Hi Yousuf

In the Sort_Total macro in Module 5 change this line of code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[yus786]

Hi John, yup that works. Let me now test with LIVE data.

But looks very good so far, thanks for that.

Obviously i dont have any live files that use this new format - so will make some now

CHeers

[jaslake]

Hi Yousuf

This code is in Module 3 of the attached and accommodates this

I want the ADD Client button to ADD a new row and DEL Client to DELETE

Please note that I've changed some of your Formulas. Let me know of issues.

Please Login or Register  to view this content.

[yus786]

Thanks John, you're a pure genius.

One thing tho when i click the new client - it does create a new line but the drop down list is at the bottom. So when i click on the arrow to select from list - it appears to be blank (when in fact it needs to scroll up)

Easy fix?

Cheers

I'll test again with proper live data now.

PS: Also John, could this be saved in xlsx? I've had a few issues with the old one where the users have saved from a xls into a xlsx and have lost the macros'

[jaslake]

Hi Yousuf
I'm either misunderstanding this or I can't duplicate it

when i click the new client - it does create a new line but the drop down list is at the bottom

This is what I see on the DropDown list...so, what am I missing?

screen shot.jpg

Also, regarding this

could this be saved in xlsx?

It could be but by definition, .xlsx files are Macro Free Workbooks. Again I may be misunderstanding the issue. I'd think your options are to save as .xls or .xlsm. If you save as .xlsm (or .xlsx) your assuming all your users are running on Excel 2007 or later. Please explain further.

[yus786]

Hi John

Yeah sorry for the confusion. Your screen shot is correct but i see this in the 1st instance blank.jpg. Then i use the scroll bars to move up. Actually thinking about it - this will sort it self out when i correct the list in data validation.

We all have excel 2007 onwards. But again that's no biggy - it can be left in xls

Cheers

[jaslake]

Hi Yousuf
Alrighty then...I'll wait to hear from you...let me know how testing is going and what issues arise. If there's something I should be paying attention to you'll need to let me know.

[yus786]

Will do, thanks John

[yus786]

Hi John

1. On the Total sheet, is it possible to run the codes as soon as the spreadsheet is opened?

2. On the Total Sheet, would it be possible to sort per either client, country or task? So we could click on the header and it sorts? So i click on the header for Client and it sorts per client?

3. On the Individual Sheet (EE1), the Clear function doesn't work properly any more. The current code is

Please Login or Register  to view this content.

However, instead of the above, what i would like is for ALL lines to be deleted apart from column 5, and would also like the hours to be zeroed. Something like this to clear.JPG. And would also like the LIST (from my Data Validation) be be on the first line. The 1st line is **Select .... **

Thanks John

[jaslake]

Hi Yousuf

Regarding this

On the Total sheet, is it possible to run the codes as soon as the spreadsheet is opened

This code is in the ThisWorkbook Module

Please Login or Register  to view this content.

You'll notice the underlined line of code is commented out...uncomment this line and the code will run when the workbook is opened.

Regarding this

On the Total Sheet, would it be possible to sort per either client, country or task?

To do this I've modified the Data such that the Formulas creating the All Totals are being replaced with Values. This MAY affect down the road capabilities...but...for the moment appears to do as required. In Add_Stuff

Please Login or Register  to view this content.

I've also added this Worksheet_BeforeDoubleClick Code to Total Sheet Code Module...double click on the Target Cell to do the sort.

Please Login or Register  to view this content.

Regarding this

On the Individual Sheet (EE1), the Clear function doesn't work properly any more

There is no clear function in the EE1 sheet that I can see...and I don't see what Column 5 has to do with anything

for ALL lines to be deleted apart from column 5

I can only assume you meant Row 5. Apart from that, do you WANT the first row to read "**Select...**" or should they be blank?

[yus786]

Thanks for this John. I'll test and report back.

Regarding the "clear" question, one of the earlier version you did had a submit function which would save the file to our servers and then clear all hours to zero.

However this time, after i click on submit (after it saves to the server and i know how to do this) the final item should be to delete all rows apart from ROW 5 (yes sorry)

And then yes i want row 5 to default to ** Select ....**

Thanks John

[jaslake]

Hi Yousuf

I've incorporated code to do this

delete all rows apart from ROW 5

Please Login or Register  to view this content.

The code is currently attached to the CLEAR Button. You can incorporate this code into the Submit code as required. If you need assistance with that let me know.
I also made some minor code changes to ADD_Client and DELETE_Client code. Take a look at the differences and see if you understand why...if not, I'll try to explain. Let me know of issues.

PS: You should also note that I added some Dynamic Named Ranges to your Validation Lists in Sheet2 and am using those Named Ranges for Data Validation.

[yus786]

Hi John

The EE1 individual sheets are fine. I've updated with live clients etc and works really well.

However the totalv2 doesn't work.

AT 1st I locked all cells in the EE1 SHeet that were not required and got this when i opened the total sheet

pw.JPG

I then took the protection off and now when I open, get the following for tasks, employee, country and client for a number of times

task.JPG

Upon debug - it goes to here:

Please Login or Register  to view this content.

Please advise

THanks John

Yousuf

[jaslake]

Hi Yousuf

Been busy today with Family matters. I believe I know what's causing the issue...will try to get back to it tomorrow.

[yus786]

Thanks John, catch you later...

[jaslake]

Hi Yousuf

Try the attached...I believe this issue is resolved

now when I open, get the following for tasks, employee, country and client for a number of times

[yus786]

Sorry John, this hasn't worked.

The first tab looks like these 2 screen shots

1. error2.jpg
2. error1.jpg.

I have removed the names

If i filter on the individual employee at the top, that looks OK but the totals seem to be adding up differently

Thanks pal

[jaslake]

Hi Yousuf

I've gone over the code, stepping through it line by line, and made the following changes in Module 5:

In the Sort_Total routine

Please Login or Register  to view this content.

And in the Add_Stuff routine

Please Login or Register  to view this content.

Both of these necessary changes would definitely affect this

the totals seem to be adding up differently

You can look at the code in the attached and see what changes I made. If we're still not working I'd like you to provide 5 or 6 employee time sheets for me to test with. I'd like to know we're using the same data.

[yus786]

Brilliant John, that seems to work. I'll test further tho.

On the Total Sheet (V3), could the data tab hide upon opening and could row 2 on the first tab be a little taller?

ANd on the EE Sheet, could you give me the code so that when they select one of the clients with a * in Column A (like * Sick), then this copies over to Columns B and C and then locks it so they can't over ride it. Something like this

lock.JPG

Thanks for all you efforts for the past months. I have no idea how i would have done this without your help. God bless you John.

[jaslake]

Hi Yousuf
Regarding this

could row 2 on the first tab be a little taller

Change it to what ever you like, save and close the file...reopen the file...the new row height will stick.
Regarding this

could the data tab hide upon opening

On opening the file, Data tab gets deleted. If you want it hidden at all times add this line of code to Module 5 Create_Data routine

Please Login or Register  to view this content.

Regarding this

so that when they select one of the clients with a * in Column A (like * Sick), then this copies over to Columns B and C and then locks it so they can't over ride it

I'll need to play with it.

[jaslake]

Hi Yousuf

Attached is the revised EE file. Please note that to accommodate Columns B & C being populated with items like "* Sick" those items needed to be added to the validation lists for Columns B & C.
Play with it...it appears to work for me.

[yus786]

Hi John, sorry for the late response. I got called away from work.

I'll have a look at this today

Thanks again for your help

[yus786]

Hi John

How can i add a password to this?

Please Login or Register  to view this content.

And on the Pivot Tab (of the Total Sheet), would it be possible to add a 'Total Hours' column? As it stands, the pivot reports Mon - Sun. But i will also need the Total Hours Column.

Great job on this too John and thanks again

[jaslake]

Hi Yousuf

This is code I use for worksheet protection

Please Login or Register  to view this content.

Attached Total File accommodates this

Total Hours Column