Formula question

[nmorph]

I am having problems entering a formula in that I continually get syntax
errors. I need to format the bar styles in the detail Gantt view to show
green for on time, yellow for close to due date and red for past due. I
understand how to use the dialog box to enter the bar styles. I have a Text1
column named 'Schedule Status' that shows green, yellow and red graphical
indicators. What is the correct way to have the bar styles reflect the data
from the schedule status column?

Any help would be greatly appreciated. I am using Project 2003.

Thanks so much!

 

[Andrew Lavinsky]

As far as I can tell, the Gantt Chart formatting is only tied to the Flag
fields. Since they're only yes/no, you would need a combination of two of
them. What you might want to do is:

1) Set Custom Flag1 to Yes, if Text1 = Green; No, if Text1 <> Green (in this
case substitute whatever calculation you're doing to determine green)
2) Set Custom Flag2 to Yes, if Text1 = Yellow; No, if Text1 = Red

Then, within the Gantt Chart formatting, add a couple of formatted bars using
the Flag1 / Not Flag1 (or Flag2) command. If you used the above schema,
it should look kind of like this:

Green = Yes, Yes/No
Yellow = No, Yes
Red = No, No

There's probably an easier way, but this should work.

-A

 

[nmorph]

Okay, so I've entered my bar styles to reflect what is in the Flag columns.
I have also entered the Yes/No data into the value list for Flag1, Flag2 and
Flag3. I'm still confused as to how the information is pulled from the
Text1 column. All three Flag columns show 'No' unless I manually enter 'Yes'.
Then I can see the correct bar styles in the view.

I don't quite understand what you mean by adding the Flag1/NotFlag 1 (or
Flag2) command. Can you give me more information as to where the Green =
Yes, Yes/No, Yellow = No, Yes, Red = No, No should be?

I'm fairly new to Project so a lot of this is very unfamiliar. Thank you so
much for your patience!

nmorph

 

[Andrew Lavinsky]

Sounds like you're almost there.....the only remaining bit is to automate
the flag fields....all you have to do there is use an if/then statement w/
the same formula you're using in the text1 field:

IIf( expression, truepart, falsepart )..... IIF(text1>X, Yes, No)....or just
plug in the same formula from text1 into the flag field. Since you want
3 permutations, and the flag fields only allow 2, you will need to use a
combination of 2 (or more) flag fields to achieve your goal (i.e. Y/Y, Y/N,
N/Y, N/N). In your case, you have used 3, which simplifies things, but perhaps
uses an extra flag field (Y/N/N, N/Y/N, N/N/Y).

I would then update the bar styles to take that into account. On the Gantt
Bar Styles, I would create a new entry and delineate it as Normal, Flag1,
Not Flag2 (or whatever combination you want to use to capture the right colors)

-A

 

[nmorph]

Hi Andrew,
I apologize - I am still quite lost here! This is how I have my setup:
For Flag1 value fields:
Value: Yes Description: IIf([Text1]='Green',"Yes")
Value: No Description: Iif([Text1]<>'Green',"No")
For Flag2 value fields:
Value: Yes Description: Iif([Text1]<>'Yellow',"Yes")
Value: No Description: Iif([Text1]<>'Yellow',"No")
For Flag3 value fields:
Value: Yes Description: Iif([Text1]<>'Red',"Yes")
Value: No Description: Iif([Text1]<>'Red',"No")
All the values in the Flag1 column are showing 'Yes' even though the
majority of items in the Text1 column are red. Flag2 and Flag3 columns are
incorrect as well. I have set up my bar styles as follows:
Task Green bar Show for Flag1 Tasks Row 1 From Start to Finish
Task Yellow bar Show for Flag2 Tasks Row 1 From Start to Finish
Task Red bar Show for Flag3 Tasks Row1 From Start to Finish

The bars will show green for the major task and red for the subtasks,
whether they are behind schedule or not.

I'm not following you on the "IIf( expression, truepart, falsepart ).....
IIF(text1>X, Yes, No)....an extra flag field (Y/N/N, N/Y/N, N/N/Y)"
instructions. Could you be a little more specific?

Again, I apologize for not understanding this. I'm sure it's something that
is quite easy, but since I'm not very familiar with formulas and how to make
the appropriate settings, I'm kind of in the dark here!

Thank you so much for all of your help - I sincerely appreciate it!

Nancy

 

[nmorph]

Hi Andrew,
Just following up to see if you have a response to my latest question.

Thanks,
Nancy

Hi Andrew,
I apologize - I am still quite lost here! This is how I have my setup:
For Flag1 value fields:
Value: Yes Description: IIf([Text1]='Green',"Yes")
Value: No Description: Iif([Text1]<>'Green',"No")
For Flag2 value fields:
Value: Yes Description: Iif([Text1]<>'Yellow',"Yes")
Value: No Description: Iif([Text1]<>'Yellow',"No")
For Flag3 value fields:
Value: Yes Description: Iif([Text1]<>'Red',"Yes")
Value: No Description: Iif([Text1]<>'Red',"No")
All the values in the Flag1 column are showing 'Yes' even though the
majority of items in the Text1 column are red. Flag2 and Flag3 columns are
incorrect as well. I have set up my bar styles as follows:
Task Green bar Show for Flag1 Tasks Row 1 From Start to Finish
Task Yellow bar Show for Flag2 Tasks Row 1 From Start to Finish
Task Red bar Show for Flag3 Tasks Row1 From Start to Finish

The bars will show green for the major task and red for the subtasks,
whether they are behind schedule or not.

I'm not following you on the "IIf( expression, truepart, falsepart ).....
IIF(text1>X, Yes, No)....an extra flag field (Y/N/N, N/Y/N, N/N/Y)"
instructions. Could you be a little more specific?

Again, I apologize for not understanding this. I'm sure it's something that
is quite easy, but since I'm not very familiar with formulas and how to make
the appropriate settings, I'm kind of in the dark here!

Thank you so much for all of your help - I sincerely appreciate it!

Nancy

Sounds like you're almost there.....the only remaining bit is to automate
the flag fields....all you have to do there is use an if/then statement w/
the same formula you're using in the text1 field:

IIf( expression, truepart, falsepart )..... IIF(text1>X, Yes, No)....or just
plug in the same formula from text1 into the flag field. Since you want
3 permutations, and the flag fields only allow 2, you will need to use a
combination of 2 (or more) flag fields to achieve your goal (i.e. Y/Y, Y/N,
N/Y, N/N). In your case, you have used 3, which simplifies things, but perhaps
uses an extra flag field (Y/N/N, N/Y/N, N/N/Y).

I would then update the bar styles to take that into account. On the Gantt
Bar Styles, I would create a new entry and delineate it as Normal, Flag1,
Not Flag2 (or whatever combination you want to use to capture the right colors)

-A

 

[JulieS]

Hi Nancy,

Pardon me for jumping in. Perhaps I can clarify and answer your
questions.

Your "Schedule Status" [Text1] field shows graphical indicators of
Green, Yellow, or Red. Assuming that the results of the formula in
[Text1] are the actual words "Green", "Yellow" or "Red":
You will need three flag fields (Flag1, Flag2, Flag3)

Flag1 formula:
IIf([Text1]= "Green", 1,0)
This will change the Flag1 field to "yes" if the Text1 field is "Green"

Flag2 formula:
IIf([Text1]= "Yellow", 1,0)
This will change the Flag2 field to "yes" if the Text1 field is "Yellow"

Flag3 formula:
IIf([Text1]= "Red", 1,0)
This will change the Flag3 field to "yes" if the Text1 field is "Red".

To change the Gantt Bars:
Go to Format > Bar Styles. Insert 3 blank lines under the Tasks line
First new line:
Name: Green Tasks
Appearance: Green bar or whatever you choose
Show for: Flag1
Row: 1
From: Start
To: Finish

Second new line:
Name: Yellow Tasks
Appearance: Yellow bar or whatever you choose
Show for: Flag2
Row: 1
From: Start
To: Finish

Third new line:
Name: Red Tasks
Appearance: red bar or whatever you choose
Show for: Flag3
Row: 1
From: Start
To: Finish

I hope this helps. Let us know how you get along.

Julie

Hi Andrew,
Just following up to see if you have a response to my latest question.

Thanks,
Nancy

Hi Andrew,
I apologize - I am still quite lost here! This is how I have my
setup:
For Flag1 value fields:
Value: Yes Description: IIf([Text1]='Green',"Yes")
Value: No Description: Iif([Text1]<>'Green',"No")
For Flag2 value fields:
Value: Yes Description: Iif([Text1]<>'Yellow',"Yes")
Value: No Description: Iif([Text1]<>'Yellow',"No")
For Flag3 value fields:
Value: Yes Description: Iif([Text1]<>'Red',"Yes")
Value: No Description: Iif([Text1]<>'Red',"No")
All the values in the Flag1 column are showing 'Yes' even though the
majority of items in the Text1 column are red. Flag2 and Flag3
columns are
incorrect as well. I have set up my bar styles as follows:
Task Green bar Show for Flag1 Tasks Row 1 From Start to
Finish
Task Yellow bar Show for Flag2 Tasks Row 1 From Start to
Finish
Task Red bar Show for Flag3 Tasks Row1 From Start to Finish

The bars will show green for the major task and red for the subtasks,
whether they are behind schedule or not.

I'm not following you on the "IIf( expression, truepart,
falsepart ).....
IIF(text1>X, Yes, No)....an extra flag field (Y/N/N, N/Y/N, N/N/Y)"
instructions. Could you be a little more specific?

Again, I apologize for not understanding this. I'm sure it's
something that
is quite easy, but since I'm not very familiar with formulas and how
to make
the appropriate settings, I'm kind of in the dark here!

Thank you so much for all of your help - I sincerely appreciate it!

Nancy

Sounds like you're almost there.....the only remaining bit is to
automate
the flag fields....all you have to do there is use an if/then
statement w/
the same formula you're using in the text1 field:

IIf( expression, truepart, falsepart )..... IIF(text1>X, Yes,
No)....or just
plug in the same formula from text1 into the flag field. Since you
want
3 permutations, and the flag fields only allow 2, you will need to
use a
combination of 2 (or more) flag fields to achieve your goal (i.e.
Y/Y, Y/N,
N/Y, N/N). In your case, you have used 3, which simplifies things,
but perhaps
uses an extra flag field (Y/N/N, N/Y/N, N/N/Y).

I would then update the bar styles to take that into account. On
the Gantt
Bar Styles, I would create a new entry and delineate it as Normal,
Flag1,
Not Flag2 (or whatever combination you want to use to capture the
right colors)

-A



Okay, so I've entered my bar styles to reflect what is in the
Flag
columns.
I have also entered the Yes/No data into the value list for
Flag1,
Flag2 and
Flag3. I'm still confused as to how the information is pulled
from
the
Text1 column. All three Flag columns show 'No' unless I manually
enter
'Yes'.
Then I can see the correct bar styles in the view.
I don't quite understand what you mean by adding the
Flag1/NotFlag 1
(or Flag2) command. Can you give me more information as to where
the
Green = Yes, Yes/No, Yellow = No, Yes, Red = No, No should be?

I'm fairly new to Project so a lot of this is very unfamiliar.
Thank
you so much for your patience!

nmorph

:

As far as I can tell, the Gantt Chart formatting is only tied to
the
Flag fields. Since they're only yes/no, you would need a
combination
of two of them. What you might want to do is:

1) Set Custom Flag1 to Yes, if Text1 = Green; No, if Text1 <>
Green
(in this
case substitute whatever calculation you're doing to determine
green)
2) Set Custom Flag2 to Yes, if Text1 = Yellow; No, if Text1 =
Red
Then, within the Gantt Chart formatting, add a couple of
formatted
bars using the Flag1 / Not Flag1 (or Flag2) command. If you
used the
above schema, it should look kind of like this:

Green = Yes, Yes/No
Yellow = No, Yes
Red = No, No
There's probably an easier way, but this should work.

-A

I am having problems entering a formula in that I continually
get
syntax errors. I need to format the bar styles in the detail
Gantt
view to show green for on time, yellow for close to due date
and red
for past due. I understand how to use the dialog box to enter
the
bar styles. I have a Text1 column named 'Schedule Status' that
shows green, yellow and red graphical indicators. What is the
correct way to have the bar styles reflect the data from the
schedule status column?

Any help would be greatly appreciated. I am using Project
2003.

Thanks so much!

 

[nmorph]

Julie,

You are my hero! Thank you SO much - this is exactly what I was looking for!

Nancy

Hi Nancy,

Pardon me for jumping in. Perhaps I can clarify and answer your
questions.

Your "Schedule Status" [Text1] field shows graphical indicators of
Green, Yellow, or Red. Assuming that the results of the formula in
[Text1] are the actual words "Green", "Yellow" or "Red":
You will need three flag fields (Flag1, Flag2, Flag3)

Flag1 formula:
IIf([Text1]= "Green", 1,0)
This will change the Flag1 field to "yes" if the Text1 field is "Green"

Flag2 formula:
IIf([Text1]= "Yellow", 1,0)
This will change the Flag2 field to "yes" if the Text1 field is "Yellow"

Flag3 formula:
IIf([Text1]= "Red", 1,0)
This will change the Flag3 field to "yes" if the Text1 field is "Red".

To change the Gantt Bars:
Go to Format > Bar Styles. Insert 3 blank lines under the Tasks line
First new line:
Name: Green Tasks
Appearance: Green bar or whatever you choose
Show for: Flag1
Row: 1
From: Start
To: Finish

Second new line:
Name: Yellow Tasks
Appearance: Yellow bar or whatever you choose
Show for: Flag2
Row: 1
From: Start
To: Finish

Third new line:
Name: Red Tasks
Appearance: red bar or whatever you choose
Show for: Flag3
Row: 1
From: Start
To: Finish

I hope this helps. Let us know how you get along.

Julie

Hi Andrew,
Just following up to see if you have a response to my latest question.

Thanks,
Nancy

Hi Andrew,
I apologize - I am still quite lost here! This is how I have my
setup:
For Flag1 value fields:
Value: Yes Description: IIf([Text1]='Green',"Yes")
Value: No Description: Iif([Text1]<>'Green',"No")
For Flag2 value fields:
Value: Yes Description: Iif([Text1]<>'Yellow',"Yes")
Value: No Description: Iif([Text1]<>'Yellow',"No")
For Flag3 value fields:
Value: Yes Description: Iif([Text1]<>'Red',"Yes")
Value: No Description: Iif([Text1]<>'Red',"No")
All the values in the Flag1 column are showing 'Yes' even though the
majority of items in the Text1 column are red. Flag2 and Flag3
columns are
incorrect as well. I have set up my bar styles as follows:
Task Green bar Show for Flag1 Tasks Row 1 From Start to
Finish
Task Yellow bar Show for Flag2 Tasks Row 1 From Start to
Finish
Task Red bar Show for Flag3 Tasks Row1 From Start to Finish

The bars will show green for the major task and red for the subtasks,
whether they are behind schedule or not.

I'm not following you on the "IIf( expression, truepart,
falsepart ).....
IIF(text1>X, Yes, No)....an extra flag field (Y/N/N, N/Y/N, N/N/Y)"
instructions. Could you be a little more specific?

Again, I apologize for not understanding this. I'm sure it's
something that
is quite easy, but since I'm not very familiar with formulas and how
to make
the appropriate settings, I'm kind of in the dark here!

Thank you so much for all of your help - I sincerely appreciate it!

Nancy


:


Sounds like you're almost there.....the only remaining bit is to
automate
the flag fields....all you have to do there is use an if/then
statement w/
the same formula you're using in the text1 field:

IIf( expression, truepart, falsepart )..... IIF(text1>X, Yes,
No)....or just
plug in the same formula from text1 into the flag field. Since you
want
3 permutations, and the flag fields only allow 2, you will need to
use a
combination of 2 (or more) flag fields to achieve your goal (i.e.
Y/Y, Y/N,
N/Y, N/N). In your case, you have used 3, which simplifies things,
but perhaps
uses an extra flag field (Y/N/N, N/Y/N, N/N/Y).

I would then update the bar styles to take that into account. On
the Gantt
Bar Styles, I would create a new entry and delineate it as Normal,
Flag1,
Not Flag2 (or whatever combination you want to use to capture the
right colors)

-A



Okay, so I've entered my bar styles to reflect what is in the
Flag
columns.
I have also entered the Yes/No data into the value list for
Flag1,
Flag2 and
Flag3. I'm still confused as to how the information is pulled
from
the
Text1 column. All three Flag columns show 'No' unless I manually
enter
'Yes'.
Then I can see the correct bar styles in the view.
I don't quite understand what you mean by adding the
Flag1/NotFlag 1
(or Flag2) command. Can you give me more information as to where
the
Green = Yes, Yes/No, Yellow = No, Yes, Red = No, No should be?

I'm fairly new to Project so a lot of this is very unfamiliar.
Thank
you so much for your patience!

nmorph

:

As far as I can tell, the Gantt Chart formatting is only tied to
the
Flag fields. Since they're only yes/no, you would need a
combination
of two of them. What you might want to do is:

1) Set Custom Flag1 to Yes, if Text1 = Green; No, if Text1 <>
Green
(in this
case substitute whatever calculation you're doing to determine
green)
2) Set Custom Flag2 to Yes, if Text1 = Yellow; No, if Text1 =
Red
Then, within the Gantt Chart formatting, add a couple of
formatted
bars using the Flag1 / Not Flag1 (or Flag2) command. If you
used the
above schema, it should look kind of like this:

Green = Yes, Yes/No
Yellow = No, Yes
Red = No, No
There's probably an easier way, but this should work.

-A

I am having problems entering a formula in that I continually
get
syntax errors. I need to format the bar styles in the detail
Gantt
view to show green for on time, yellow for close to due date
and red
for past due. I understand how to use the dialog box to enter
the
bar styles. I have a Text1 column named 'Schedule Status' that
shows green, yellow and red graphical indicators. What is the
correct way to have the bar styles reflect the data from the
schedule status column?

Any help would be greatly appreciated. I am using Project
2003.

Thanks so much!

 

[JulieS]

Hi Nancy,

You're very welcome and thanks for the enthusiastic feedback.

Julie

Julie,

You are my hero! Thank you SO much - this is exactly what I was
looking for!

Nancy

Hi Nancy,

Pardon me for jumping in. Perhaps I can clarify and answer your
questions.

Your "Schedule Status" [Text1] field shows graphical indicators of
Green, Yellow, or Red. Assuming that the results of the formula in
[Text1] are the actual words "Green", "Yellow" or "Red":
You will need three flag fields (Flag1, Flag2, Flag3)

Flag1 formula:
IIf([Text1]= "Green", 1,0)
This will change the Flag1 field to "yes" if the Text1 field is
"Green"

Flag2 formula:
IIf([Text1]= "Yellow", 1,0)
This will change the Flag2 field to "yes" if the Text1 field is
"Yellow"

Flag3 formula:
IIf([Text1]= "Red", 1,0)
This will change the Flag3 field to "yes" if the Text1 field is
"Red".

To change the Gantt Bars:
Go to Format > Bar Styles. Insert 3 blank lines under the Tasks line
First new line:
Name: Green Tasks
Appearance: Green bar or whatever you choose
Show for: Flag1
Row: 1
From: Start
To: Finish

Second new line:
Name: Yellow Tasks
Appearance: Yellow bar or whatever you choose
Show for: Flag2
Row: 1
From: Start
To: Finish

Third new line:
Name: Red Tasks
Appearance: red bar or whatever you choose
Show for: Flag3
Row: 1
From: Start
To: Finish

I hope this helps. Let us know how you get along.

Julie

Hi Andrew,
Just following up to see if you have a response to my latest
question.

Thanks,
Nancy

:

Hi Andrew,
I apologize - I am still quite lost here! This is how I have my
setup:
For Flag1 value fields:
Value: Yes Description: IIf([Text1]='Green',"Yes")
Value: No Description: Iif([Text1]<>'Green',"No")
For Flag2 value fields:
Value: Yes Description: Iif([Text1]<>'Yellow',"Yes")
Value: No Description: Iif([Text1]<>'Yellow',"No")
For Flag3 value fields:
Value: Yes Description: Iif([Text1]<>'Red',"Yes")
Value: No Description: Iif([Text1]<>'Red',"No")
All the values in the Flag1 column are showing 'Yes' even though
the
majority of items in the Text1 column are red. Flag2 and Flag3
columns are
incorrect as well. I have set up my bar styles as follows:
Task Green bar Show for Flag1 Tasks Row 1 From Start to
Finish
Task Yellow bar Show for Flag2 Tasks Row 1 From Start to
Finish
Task Red bar Show for Flag3 Tasks Row1 From Start to
Finish

The bars will show green for the major task and red for the
subtasks,
whether they are behind schedule or not.

I'm not following you on the "IIf( expression, truepart,
falsepart ).....
IIF(text1>X, Yes, No)....an extra flag field (Y/N/N, N/Y/N,
N/N/Y)"
instructions. Could you be a little more specific?

Again, I apologize for not understanding this. I'm sure it's
something that
is quite easy, but since I'm not very familiar with formulas and
how
to make
the appropriate settings, I'm kind of in the dark here!

Thank you so much for all of your help - I sincerely appreciate
it!

Nancy


:


Sounds like you're almost there.....the only remaining bit is to
automate
the flag fields....all you have to do there is use an if/then
statement w/
the same formula you're using in the text1 field:

IIf( expression, truepart, falsepart )..... IIF(text1>X, Yes,
No)....or just
plug in the same formula from text1 into the flag field. Since
you
want
3 permutations, and the flag fields only allow 2, you will need
to
use a
combination of 2 (or more) flag fields to achieve your goal
(i.e.
Y/Y, Y/N,
N/Y, N/N). In your case, you have used 3, which simplifies
things,
but perhaps
uses an extra flag field (Y/N/N, N/Y/N, N/N/Y).

I would then update the bar styles to take that into account.
On
the Gantt
Bar Styles, I would create a new entry and delineate it as
Normal,
Flag1,
Not Flag2 (or whatever combination you want to use to capture
the
right colors)

-A



Okay, so I've entered my bar styles to reflect what is in the
Flag
columns.
I have also entered the Yes/No data into the value list for
Flag1,
Flag2 and
Flag3. I'm still confused as to how the information is
pulled
from
the
Text1 column. All three Flag columns show 'No' unless I
manually
enter
'Yes'.
Then I can see the correct bar styles in the view.
I don't quite understand what you mean by adding the
Flag1/NotFlag 1
(or Flag2) command. Can you give me more information as to
where
the
Green = Yes, Yes/No, Yellow = No, Yes, Red = No, No should be?

I'm fairly new to Project so a lot of this is very unfamiliar.
Thank
you so much for your patience!

nmorph

:

As far as I can tell, the Gantt Chart formatting is only tied
to
the
Flag fields. Since they're only yes/no, you would need a
combination
of two of them. What you might want to do is:

1) Set Custom Flag1 to Yes, if Text1 = Green; No, if Text1 <>
Green
(in this
case substitute whatever calculation you're doing to
determine
green)
2) Set Custom Flag2 to Yes, if Text1 = Yellow; No, if Text1 =
Red
Then, within the Gantt Chart formatting, add a couple of
formatted
bars using the Flag1 / Not Flag1 (or Flag2) command. If you
used the
above schema, it should look kind of like this:

Green = Yes, Yes/No
Yellow = No, Yes
Red = No, No
There's probably an easier way, but this should work.

-A

I am having problems entering a formula in that I
continually
get
syntax errors. I need to format the bar styles in the
detail
Gantt
view to show green for on time, yellow for close to due date
and red
for past due. I understand how to use the dialog box to
enter
the
bar styles. I have a Text1 column named 'Schedule Status'
that
shows green, yellow and red graphical indicators. What is
the
correct way to have the bar styles reflect the data from the
schedule status column?

Any help would be greatly appreciated. I am using Project
2003.

Thanks so much!