Formula contains and ERROR???

[Legacy 259187]

Hello, I am trying to displace 3 different results, 0,1 or "", based on 4 arguments. The formula I am trying to do this with is =IF(AND(B$2>=Data!$L2,B$2<=Data!$W2),1,"",IF(AND (B$2>=Data!$M2,B$2<=Data!$P2),0,"")). Excel is telling me I an error. I must be missing something. This is for a gantt chart that I want to display results in a way that the chart tool cant help me with so I am having to try to build this from scratch. Looks like I have hit a wall. any help to point out the error here would be most appreciated.

 

[Legacy 259187]

What I am going to do is use the conditional format to color display a different fill color for each 1 and 0 to create a bar display to reflect pogression based on current dates verses the duration of the project milestones. I am also going to include a Data date line to be displayed on the column that corresponds to the current date to show behind or ahead of schedule which I cannot figure out how to do using the excel chart tool.

 

[Legacy 259187]

Progression***

 

[Andrew Poulsom]

Is this what you want?

=IF(B$2>=Data!$L2,IF(B$2<=Data!$P2,0,IF(B$2<=Data!$W2,1,"")),"")

 

[Legacy 259187]

Ok, new addition to this problem.. here are the formulas that I have: =IF(AND(C$2>=Data!$M3,C$2<=Data!$X3),1,IF(AND(C$2>=Data!$X3,C$2<=Data!$P3),0,"")) & =IF(AND(C$2>=Data!$V4,C$2<=Data!$W4),1,IF(AND(C$2>=Data!$W4,C$2<=Data!$O4),2,"")) What I need to do is combine both of these and have only one or the other applied based on key words in column B. If "Assembley" and "CA0" is found in column B then have only the first formula apply. if "S2" and "V3" are found in column B only the second formula shown here should apply. If this possible and if so please HELP!.

 

[Andrew Poulsom]

Please post some sample data and the expected results. I don't know what you mean by "found in column B".

 

[Legacy 259187]

BAY 6 & 7 GANTT FLOW CHART

T

W

T

F

S

S

M

T

W

T

F

S

S

8-Oct-13

26

27

28

29

30

31

01

02

03

04

05

06

07

SUB-MODULE NUMBER

31-Mar-13

7-Apr-13

CA01-07 Wall Sub-Module - Summer 2

FAB.100 Sub Assembly 100

S2-01-07 FAB.100

Final Final Assembly

S2-01-07 Final

CA01-07 Wall Sub-Module - Vogtle 3

FAB.100 Sub Assembly 100

V3-01-07 FAB.100

Final Final Assembly

V3-01-07 Final

<colgroup><col style="width: 185pt; mso-width-source: userset; mso-width-alt: 8996;" width="246"> <col style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;" span="13" width="19"> <tbody>
</tbody>

 

[Legacy 259187]

Column B is the first colume shown. I need the formulas to place the Data according to what is listed in that column and if the key word is "assembly" or "Cao" then only the first formula listed in the previous post should apply if "S2" or "V3" is there then only the second formula would apply. I need it to read off of another sheet where the data exists and return the results as stated in the formula. This is so i can export the data required from The scheduling software I use wuith out having to make repaste formulas every time I make an update

 

[Legacy 259187]

The chart I built is rather large and for what ever reason it will not let me post. In the first formula. if the condition exist then a 1 or 0 is place in the column according to the date that is listed in row C otherwise it is left Blank. the second formula place a 1 or 2 or blank in the same perspective as the first formula. I then used conditional formatting to draw the length and color of the bar to show duration and progression. what I am aiming to do is combine both formulas so when i do a data dump I do not have to repaste the formulas based on the row location of the items in column B because the data will shift as items are added or removed. I need the formula to follow accordingly

 

[Andrew Poulsom]

To test if A1 begins with "S2" or "V3":

=IF(OR(LEFT(A1,2)="S2",LEFT(A1,2)="V3") ...

To test if A1 contains "Assembly" or "CA0":

=IF(OR(ISNUMBER(FIND("Assembly",A1)),ISNUMBER(FIND("CA0",A1))) ...

 

[Legacy 259187]

Can all the formulas be combined into one standing formula? If so, how would that be done with out getting the too many arguments error?
=IF(OR(LEFT(A1,2)="S2",LEFT(A1,2)="V3") & =IF(OR(ISNUMBER(FIND("Assembly",A1)),ISNUMBER(FIND("CA0",A1))) & =IF(AND(C$2>=Data!$M3,C$2<=Data!$X3),1,IF(AND(C$2>=Data!$X3,C$2<=Data!$P3),0,"")) & =IF(AND(C$2>=Data!$V4,C$2<=Data!$W4),1,IF(AND(C$2>=Data!$W4,C$2<=Data!$O4),2,""))