Hi,


I have MS Excel 2007 and I have to prepare my team availability matrix for superiors. But I am not a user friendly about formulas and macros. So need your help.

Formula’s or macro required for solving my query.

I have two tables:
Table A - Present Available manpower count

Jun-15 Jul-15 Aug-15 Sep-15
Skill1 1 2 5 1
Skill2 3 3 1 1
Skill3 2 2 2 1

Table B - New Program Requirement

Skill1 Skill2 Skill3
Prog1 2 1 1
Prog2 2 3 3

Problem / Query : I want that, if I am selecting from drop drown list a program name (example Prog1), and selecting month (example Jul-15) from another dropdown list . On the basis of both selection, it subtract the value in Table A from Jul-15 to till Sep-15 in Skill1 , Skill2, Skill3 as per value available in Table B Skill1, Skill2, Skill3 of Prog1.

Selection of program and month is dynamic , may be Prog2 and Month Jun-15 selected , then it will subtract value from month Jun-15.


Result will like this -

Program Prog1 Selection Month Jul-15

Table A - Present Available manpower count (Subtract value)

Jun-15 Jul-15 Aug-15 Sep-15
Skill1 1 0 3 -1
Skill2 3 2 0 0
Skill3 2 1 1 0

I need to select only Program name and Date and I have a fixed format of table (please see the attached sheet - green highlighted area), month is not dynamic in table.
If I select prog1 and Jul-15, then in result table it shows Jun-15 data as-it-is but it is subtracting data from Jul-15. If Im selecting Aug-15 then JUn'15 and Jul'15 count shows as-it-is and subtracting from Aug'15 onwards.

Please see attached sheet for reference.

Thanks

Attached Files

Subtract value based on two different conditions.xlsx (8.8 KB, 15 views)