Data Validation, Lookup, multi lookup, referancing and other.

Hello all,

everything is explained in the attachments, Please have a look.

1.jpg
2.jpg

Just heads up, The past week was the first time I've tried using excel as it was intended to be used. So excuse my ignorance if I ask too many questions.

We need to set up what we call "Manpower Distribution Table" that represents employees workload vs. active projects.

ALL Projects and employee names are list data-validation cells that are grabbing information from other cells on the same sheet. reason being is that I would like to keep file easy to use as new employees/projects enter and leave the company.

And the reason that projects is broken in two columns is i want to keep the sheet short (big list of employees)

My boss is not an excel user either so I would like to keep this file as simple as possible (idiot proof) with only names and percentage to input.

Thank you in advance for your help.

As this is your first post, the attached might help.

Pete

Originally Posted by Pete_UK

As this is your first post, the attached might help.

Pete

My apologies, I thought screen grabs would suffice.
There you go.

Anyone, please?

Originally Posted by Jean.P28

like this, I hope

Yes, this definitely answers my question thank you, Jean

Originally Posted by Jean.P28

like this, I hope

=IF(ISERROR(INDEX(INDEX(DataProject,,3),MATCH(M$7&$K8,INDEX(DataProject,,1)&INDEX(DataProject,,2),0))),0,INDEX(INDEX(DataProject,,3),MATCH(M$7&$K8,INDEX(DataProject,,1)&INDEX(DataProject,,2),0)))

You're over complicated.


Try this simplify/elegant version:

=SUMIFS($F:$F,$D:$D,M$7,$E:$E,$K8)