Nested IF/OR/AND Statement Help

[Guest]

I have a schedule with each project listed and all resources assigned to each
project by area of expertise (AOE). The top of the schedule represents the
weeks of the year by dates (rows 7 and 8). I assign a start date and end
date next to each resource in columns G and H, depending on where the date
falls in rows 7 and 8 it gives me a 1 in that corresponding cell next to that
resource. It also populates a 1 in each cell thereafter until it reaches the
end date assigned.

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 all
my resources I assign
IF(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,""))

Now for the additional condition. If I am short a resource to assign, I
provide it a generic name in column F like MX-XXX, STA-XXX, etc. Based off
AOE. I want a formula that can recognize these generic names that I have
predefined and provide a -1 in place of the 1 due to the shortage.

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.

Here is an example.

5/27/07 6/3/07 ...... 7/15/07

5/21/07 5/28/07 ...... 7/9/07

D F G H
PROJECT
D.Baker 06/02/07 12/28/07 1
.............................

MX-XXX 06/03/07 7/15/07 -1
................. -1

 

[Guest]

...guessing a bit here .....that there is a missing IF ...

=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,IF(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,""))

 

[Guest]

Thanks for the help - but still no change.

..guessing a bit here .....that there is a missing IF ...

=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,IF(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,""))

 

[Guest]

Can you send a sample w/book?

toppers <at> johntopley.fsnet.co.uk