We help IT Professionals succeed at work.
Get Started

Trying to build a formula in a table to cross reference multiple data fields (Work out Loading of resources across multiple projects by month)

243 Views
Last Modified: 2016-02-11
This one is probably a lot simpler than I think, and I'm guessing I've massively over complicated everything.
Basically I have a workbook of tables of data. The tables record details of staff, their monthly working hours, current projects, what staff are assigned to those and how many hours they have a month on each project, what percentage of their total availability that is etc

Tables: Projects: This is where I list all the projects, and who is assigned, and how many hours
Tables: Resource: This is the list of staff, and a divider to get their weekly hours (From 40 max, so set to 2 would mean their weekly hours are 20)
Tables: WorkHours: This calculates using network days how many working hours there are in a given month, and reduces that by bankholidays etc
Tables: MonthsActive: This calculates what months each project is live for
Tables: ResourceLoading: Should calculate total hours Loading for each member of staff for each month

Resource loading is where my issue is. I'm stuck trying to build any kind of formula to do this.  Last resort, I'll go VBA, but if this is possible to do in the worksheet with Formulae, I'd rather go that route.

Workbook attached.
staff-loading-look-ahead---declassi.xlsm
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE