Nesting multiple formulas

[Zach]

Need some help with my situation. I've inserted a new column to capture
overall status of specific tasks within project. Basically Red for overdue
tasks; Blue for tasks not yet started; White for completed tasks, and green
for tasks that are on track. Right now it's a manual process, as I've gone
to custome fields and created a value list. I'm trying to set up a automated
process based on dates and % complete. I've gotten individual formulas to
work, but I'm not sure how to nest all of them together. Here are my
formulas:

IIf([% Complete]=100,"white")
IIf([Start]>[Current Date],"blue")
IIf([Finish]<[Current Date] And [% Complete]<100,"red").

Any help to get these to work together would be great.

 

[Gérard Ducouret]

Hello Zach,

You can adapt this formula that I tested in the French version of Project:
IIf([% achevé]=100;"white";IIf([Début]>[Date du jour];"blue";IIf([Fin]<[Date
du jour] And [% achevé]<100,"red","")))
Don't worry about the mix of "," and ";" : the French version likes this
salad.
Hope this helps,

Gérard Ducouret

 

[Zach]

Gérard,

Thanks so much. I translated and re-entered it and the formula worked
perfectly. I do have a follow up question if you can help with that. Each
task will not need a status (red, green, yellow, etc). in the Status Column.
I have another column that I inserted that I can set which lines need that
level of detail. How do I incorporate the forumla that you helped me with to
exclude the tasks that my other column lists as not needed. For example:
Line 1500 on the project plan needs to be reported on, so my Report flag
column is marked as "Yes". Line 1501 does not need to be reported so it's
flagged as "no". For the lines that have "No", how do I exclude the formula?
Basically I want the formula to say:

"If Flag 7 says "yes", apply the formula (IIf([%
Complete]=100,"white",IIf([Start]>[Current Date],"blue",IIf([Finish]<[Current
Date] And [% Complete]<100,"red",IIf([Current Date]>[Finish]-10 And [%
Complete]<75,"yellow","green"))))

If Flag 7 says "no" enter "N/A".


Thanks,
Zach

Hello Zach,

You can adapt this formula that I tested in the French version of Project:
IIf([% achevé]=100;"white";IIf([Début]>[Date du jour];"blue";IIf([Fin]<[Date
du jour] And [% achevé]<100,"red","")))
Don't worry about the mix of "," and ";" : the French version likes this
salad.
Hope this helps,

Gérard Ducouret

Need some help with my situation. I've inserted a new column to capture
overall status of specific tasks within project. Basically Red for overdue
tasks; Blue for tasks not yet started; White for completed tasks, and green
for tasks that are on track. Right now it's a manual process, as I've gone
to custome fields and created a value list. I'm trying to set up a automated
process based on dates and % complete. I've gotten individual formulas to
work, but I'm not sure how to nest all of them together. Here are my
formulas:

IIf([% Complete]=100,"white")
IIf([Start]>[Current Date],"blue")
IIf([Finish]<[Current Date] And [% Complete]<100,"red").

Any help to get these to work together would be great.

 

[Gérard Ducouret]

Hi Zach,

Try this one :
IIf([Flag7]=True;"Formula";ProjDateValue('NA'))
or, more simple:
IIf([Flag7]=True;"Formula";"N/A")

Gérard Ducouret

Gérard,

Thanks so much. I translated and re-entered it and the formula worked
perfectly. I do have a follow up question if you can help with that. Each
task will not need a status (red, green, yellow, etc). in the Status Column.
I have another column that I inserted that I can set which lines need that
level of detail. How do I incorporate the forumla that you helped me with to
exclude the tasks that my other column lists as not needed. For example:
Line 1500 on the project plan needs to be reported on, so my Report flag
column is marked as "Yes". Line 1501 does not need to be reported so it's
flagged as "no". For the lines that have "No", how do I exclude the formula?
Basically I want the formula to say:

"If Flag 7 says "yes", apply the formula (IIf([%
Complete]=100,"white",IIf([Start]>[Current Date],"blue",IIf([Finish]<[Current
Date] And [% Complete]<100,"red",IIf([Current Date]>[Finish]-10 And [%
Complete]<75,"yellow","green"))))

If Flag 7 says "no" enter "N/A".


Thanks,
Zach

Hello Zach,

You can adapt this formula that I tested in the French version of Project:
IIf([% achevé]=100;"white";IIf([Début]>[Date du jour];"blue";IIf([Fin]<[Date
du jour] And [% achevé]<100,"red","")))
Don't worry about the mix of "," and ";" : the French version likes this
salad.
Hope this helps,

Gérard Ducouret

Need some help with my situation. I've inserted a new column to capture
overall status of specific tasks within project. Basically Red for overdue
tasks; Blue for tasks not yet started; White for completed tasks, and green
for tasks that are on track. Right now it's a manual process, as I've gone
to custome fields and created a value list. I'm trying to set up a automated
process based on dates and % complete. I've gotten individual formulas to
work, but I'm not sure how to nest all of them together. Here are my
formulas:

IIf([% Complete]=100,"white")
IIf([Start]>[Current Date],"blue")
IIf([Finish]<[Current Date] And [% Complete]<100,"red").

Any help to get these to work together would be great.

 

[Zach]

Gérard,

Thanks again, but it doesn't seem to like that formula. Here's what I put
in, (obviously changed the ; to ,):

IIf([Flag7]="No","IIf([% Complete]=100,"white",IIf([Start]>[Current
Date],"blue",IIf([Finish]<[Current Date] And [%
Complete]<100,"red",IIf([Current Date]>[Finish]-10 And [%
Complete]<75,"yellow","green"))))","N/A")


It gets the IIf([Flag7] = "No", "N/A") and enters the N/A's as needed, but
with the IIf([Flag7] = "No", "(Formula)","N/A") it doesn't. I keep getting
syntax errors that highlight the white, red, green, yellow text. Any ideas?
Also, how do I mark your first response in the newsgroup with the "Answered"
symbol?

Zach

Hi Zach,

Try this one :
IIf([Flag7]=True;"Formula";ProjDateValue('NA'))
or, more simple:
IIf([Flag7]=True;"Formula";"N/A")

Gérard Ducouret

Gérard,

Thanks so much. I translated and re-entered it and the formula worked
perfectly. I do have a follow up question if you can help with that. Each
task will not need a status (red, green, yellow, etc). in the Status Column.
I have another column that I inserted that I can set which lines need that
level of detail. How do I incorporate the forumla that you helped me with to
exclude the tasks that my other column lists as not needed. For example:
Line 1500 on the project plan needs to be reported on, so my Report flag
column is marked as "Yes". Line 1501 does not need to be reported so it's
flagged as "no". For the lines that have "No", how do I exclude the formula?
Basically I want the formula to say:

"If Flag 7 says "yes", apply the formula (IIf([%
Complete]=100,"white",IIf([Start]>[Current Date],"blue",IIf([Finish]<[Current
Date] And [% Complete]<100,"red",IIf([Current Date]>[Finish]-10 And [%
Complete]<75,"yellow","green"))))

If Flag 7 says "no" enter "N/A".


Thanks,
Zach

Hello Zach,

You can adapt this formula that I tested in the French version of Project:
IIf([% achevé]=100;"white";IIf([Début]>[Date du jour];"blue";IIf([Fin]<[Date
du jour] And [% achevé]<100,"red","")))
Don't worry about the mix of "," and ";" : the French version likes this
salad.
Hope this helps,

Gérard Ducouret

"Zach" <[email protected]> a écrit dans le message de
Need some help with my situation. I've inserted a new column to capture
overall status of specific tasks within project. Basically Red for
overdue
tasks; Blue for tasks not yet started; White for completed tasks, and
green
for tasks that are on track. Right now it's a manual process, as I've
gone
to custome fields and created a value list. I'm trying to set up a
automated
process based on dates and % complete. I've gotten individual formulas to
work, but I'm not sure how to nest all of them together. Here are my
formulas:

IIf([% Complete]=100,"white")
IIf([Start]>[Current Date],"blue")
IIf([Finish]<[Current Date] And [% Complete]<100,"red").

Any help to get these to work together would be great.