Question on formula of matching Project Workload vs Resource Capac

[Morocco Mole]

Hello.

I'd like to ask for some ideas on how to do the following.

I'm trying to build a report that will match multiple projects and required
"workload days needed" against multiple "workload available" resources.

A simple layout of the Excel would be in 2 blocks of data.

1st is:
Project, Project Ranking (what should be done first), resource assigned,
project days needed and Can it be done? determination.

Project Ranking Resource Project Days Needed Can Be Done?
A 1 Manuel 10 Yes
B 2 Cindi 10 Yes
C 3 Cindi 10 Yes
D 4 Cindi 10 Yes
E 5 Cindi 10 Yes
F 6 Maynard 10 Yes
G 7 Maynard 10 Yes
H 8 Maynard 10 No
I 9 Cindi 10 No

The second table matches Project Days needed against available resources.

Resource Project Days Available Project Days Needed Delta
Manuel 20 10 10
Cindi 40 50 -10
Maynard 20 30 -10

I can get the days needed from the sumif function.

My questions:
1) What would be the formula to write for the "Can Be Done?" column in the
first block of data? I have a concept of tracking the cumulative days needed
by person to compare against days available... but I'm stuck on the formula.

2) Would the resulting formula recommended be robust enough if I had to
resort the rankings?

3) Can this be done in a pivot table?

Thanks to this great community for all its help!

 

[Squeaky]

HI Morroco,

I set this up exactly as you have described it on a spreadsheet.
I have made some assumptions. The ranking determines the order of the
projects. The second table is on the same spreadsheet. You can adjust the
formula if it is not. First, I have placed all the headers in the first table
in row 1 starting at A1 to E1. The second table starts at H1 to K1. I used
the information you gave so you may need to make adjustments to your actual
size.

You will need a "counter" set up in column F. You can hide this column
later. At F2 place (without the "")

"=COUNTIF($C$2:C2,C2)"

Copy-drag this formula down as far as you need it to go. In your 1st table,
row 10 will have the last formula and it will look like

"=COUNTIF($C$2:C10,C10)"

You should see numbers counting in column F. 1, 1, 2, 3, 4, 1, 2, 3, 5. What
this does is count the number of times each name has appeared down the list.
In cell E2 place

"=IF(D2*F2<=(VLOOKUP(C2,($H$2:$I$4),2,FALSE)),"yes","no")"

Copy-drag this down to the bottom of your list. The part "$H$2:$I$4" in the
formula is the range of the information in your second table from the
Resource to the Project Days Available. So if you have more people you will
need to adjust the "$I$4" part.

Let me know how it works.

Squeaky

 

[Morocco Mole]

Awesome. That worked. Thanks Squeeky.