Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF/OR/AND statement
Could someone please help me put this IF statement together.
I have a schedule with each project and all resources assigned to that project by area of expertise (AOE). Columns G and H are the dates that resource is assigned to start and end that project and each resource has different start and end dates. Rows 7 and 8 are basically the dates of the calender starting with the first Monday of that year and ending with the last Sunday of that year. My formula is based off the start and end dates I assign on the resource which will put a 1 in that block of the calender (cell) where the start date is found in row 8, it puts a 1 in each cell of the calender for that resource until gets to the cell where the end date is found in row 7. Column F is the resources name. Confusing? Column F = Resource Name Column G = Start Dates Column H = End Dates Row 7 = Date of end of week (Sunday) Row 8 = Date of beginning of week (Monday) My formula started like this and it works fine, I want this to work for any name of resource I assign. IF(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8=$G3 6,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)),1,"" )) Now for the additional condition. If the resource name is MX-XXX (I have many variations of XXX names depending on AOE - I can create a formula for each one - STA-XXX, OPS-XXX, etc), which means that type resource name is TBD, I want it to put a -1 instead of a 1 in the same cells as the formula does above. This -1 shows a resource shortage for me which is used in other calculations. I attempted this addition to the formula which did not work, but I just may have the syntax incorrect. IF(OR(AND($G36=Z$8,$G36<=Z$7, $F36="MX-XXX"),AND($I$8:$BH$8=$G36,$I$7:$BH$7<=$H36, $F36="MX-XXX"),AND($H36=Z$8,$H36<=Z$7,$F36="MX-XXX")),-1,"")),(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8 =$G36,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)) ,1,"")) Please help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF statement | Excel Worksheet Functions | |||
Nested IF statement | Excel Worksheet Functions | |||
how to do a nested if statement | Excel Discussion (Misc queries) | |||
Nested IF Statement | Excel Discussion (Misc queries) | |||
Nested if then else statement | Excel Worksheet Functions |