Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-01-2015, 03:28 AM
nitemath2 nitemath2 is offline How to Subtract value in table, based on two different conditions Windows 7 32bit How to Subtract value in table, based on two different conditions Office 2007
Novice
How to Subtract value in table, based on two different conditions
 
Join Date: Jun 2015
Posts: 1
nitemath2 is on a distinguished road
Post How to Subtract value in table, based on two different conditions

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
File Type: xlsx Subtract value based on two different conditions.xlsx (8.8 KB, 13 views)
Reply With Quote
  #2  
Old 06-19-2015, 01:28 AM
Catalin.B Catalin.B is offline How to Subtract value in table, based on two different conditions Windows Vista How to Subtract value in table, based on two different conditions Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

Hi,
Try this formula in cell E16, copied down and to the right:
=INDEX($A$2:$E$5,MATCH($D16,$A$2:$A$5,0),MATCH(E$1 5,$A$2:$E$2,0))-IF(E$15>=$H$12,INDEX($J$2:$M$4,MATCH($D$12,$J$2:$J $4,0),MATCH($D16,$J$2:$M$2,0)),0)

Cheers,
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Subtract value in table, based on two different conditions Table, font color based on what word is in cell donaldadams1951 Word Tables 1 04-15-2015 08:37 PM
How to Subtract value in table, based on two different conditions How to Extract Data from table based on pattern. PRA007 Word Tables 4 03-17-2015 11:05 PM
update one embedded excel table based on another excel table ajetrumpet Word 0 07-05-2013 08:51 PM
How to Subtract value in table, based on two different conditions Update Word table based on another table input mpdsal Word VBA 10 10-29-2012 07:40 AM
Formula to subtract one month from due date field in reminder field ghumdinger Outlook 1 10-01-2011 12:09 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft