Labor calculation per week.

[bb8371]

I'm not sure where to even begin in solving this issue.
The attached workbook has a "Summary" page showing. When you click the red dot it opens a hidden sheet for that project. Labor hours are entered on that sheet. What I need is something on the "LABOR" tab where I can enter a date range and it will pull any labor entered on the hidden sheets along with the project number from that sheet. Right now the person who bills out the labor has to open each record to see if labor has been added. Trying to simplify the process for him.

[Ochimus]

Not tested but try this:

Enter your Start and End dates into B1 and B2 on the Labour sheet (dd/mm/yy), then run Macro.

It opens the worksheets set in your various Hyperlink values, and extracts the Project Number and Labour Costs IF there are any, and the work did not end before your Start date, or start after your End date:

Please Login or Register  to view this content.

Hope it helps

Ochimus

[bb8371]

That's the general idea for sure. But what I'm looking for it to pull specifically is on each record there is a table that has hours entered. I'm looking to pull hours over just from a date range. The table itself has 4 sections where the employees enter their hours depending on what the did specifically. engineering, welding, machining and assembly. So if during a specific week range 6 guys worked on 10 projects in all areas I need all those hours pulled. I'd like to create a user box so he can click a button enter the date range and hit submit to have the data pulled.

[Ochimus]

bb,

That should be straightforward. As far as I can see, the Tables start on different rows in different sheets, but the word "Name" appears only in Col A at the top of the Table?

I'll try and put together a Macro that opens up a sheet, finds "Name", and runs down the rows summing up the hours worked in each of the four categories by each individual where the date in Col B is inside your Date Span, which can then be entered into a Grid in the Labour sheet showing the total hours in each Category by each Name in that period?

If it works for one, you can then add the "loop" that tells it to open, work on, and close each worksheet in turn.

Ochimus

[bb8371]

Thank you.

[Ochimus]

bb,

Can you clarify two points with the worksheets, please?

I assumed the hours in Cols C - F in the Hours section of the worksheet were all worked on whatever date is in Col B. But C75 on sheet "8" shows Chris Huck worked 24 hours on Engineering, suggesting it covered more than one day (unless he worked continuously from Midnight to Midnight?)

Secondly, that record has no date in Col B, so the Macro will ignore those twenty four hours because it will not be counted as falling between whatever Start and End dates you select?

Look forward to hearing from you

Ochimus

[bb8371]

He had entered his cumulative hours for that project. Going forward it won't be an issue once I explain they need to enter daily.

[bb8371]

I made some changes to the code below but I am still stuck. It's not working properly. On the "Labor" worksheet when the command button "Run Labor Report" is pressed it pulls the Total Labor dollars from each "Record" (individual worksheets). I added to each Record a total column on the Labor table and pulled the total overall number up to cell O1. Now that I'm talking this out, I realize this won't work. This is what I need the Run Labor Report button to pull. I only want the TOTAL hours for the week of the start and end dates listed in B1 and B2 on the Labor worksheet. I don't want Labor Dollars pulled just total hours, and only for the dates listed. Is there a way to pull information from the labor table on each record. These tables will continue to grow so how can I pull information just from the table itself.

[Ochimus]

bb8371,

Needs two quick answers so I can tweak the Code I gave you in Post#2.

Do all the sheets start the hours in row 32, or does that vary? Not an issue if it does, as A31 is the only cell saying "Name", but would make the Macro simpler.

Are the Projects listed in Col A of the Labour sheet permanent, with new jobs being added to the next free row? Or are they the Projects worked on in the day in question?

If you can explain I can revise the Macro I gave you in Post#2 to produce what you want

Ochimus

Ochimus