Hi there,
Is there a formula which can allocate the employee hrs exactly to the available job hrs on the attached excel?
I think it's a MIN or MAX formula, which will suit but not entirely sure.
Thanks,
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Formula help
0
Answers
0
It's not entirely clear what you want to do but here is an idea.
- Write the hours in column A (as you have done already)
- Creater a column for each job, starting in column C, writing the job number in row 1 (this you have also already done)
- Use column B to write the job number next to each time.
For example, 16.75 hours recorded in A4 should be assigned to job 7421.
Therefore you write 7421 in B4. - Now write this formula in C4
Copy this formula from C4 to the right and down as far as you need.=IF($B4=C$1,$A4,"")
The hours in column A are now shown in the columns below the job numbers. If you change the job number in column B the hours will move to another column. - Now write this formula in C2
Copy to the right as far as you have job numbers in row 1. Increase the end row (here 1000) as much as you might need + some more.=SUM(C$4:C$1000)
This formula will show the total hours attributed to each job. The total will change when changes are made in columns A:B.