Forum Discussion
Post ProdigyI need help calculating values bases on another column
Your first argument to SWITCH is what will be evaluated. You should ONLY use Project[StageName].
Then your values is what will be compared to your first argument. So in your code remove = "Suspect" and instead start the formula like this:
StageName2 = Switch(Project[StageName];
"Suspect";"1.Suspect";
...
...
For this purpose I think you should use Switch instead, you say you have tried but maybe you got it wrong.
Try the following code instead:
StageName2=SWITCH([Project[StageName],
"Suspect","1.Suspect",
"Prospect","2.Prospect",
"RFI received (Qualify)","3.RFI received (Qualify)",
...
)
Post ProdigyAnonymous
I tried your formula but now it gives a syntax error which i can't find.
I have to use ; instead of the , but even i change the error remains the same
StageName2 = Switch(Project[StageName] = "Suspect" ; "1.Suspect";
"Prospect"; "2.Prospect";
"RFI received (Qualify)"; "3.RFI received (Qualify)";
"Qualified"; "4.Qualified");
"RFQ received"; "5.RFQ received");
"Bid Made"; "6 Bid Made");
"Order"; "7.Order");
"Lost Order"; "8.Lost Order");
"No Bid"; "9.No Bid");
"Project Withdrawn"; "10.Project Withdrawn");
"No stageName"))
In your code there seems to be a few ) that shouldn't be there.
Only use Switch().
all other () should be inside your text-quotes "" as in example "RFI received (Qualify)"
- RvdHeijden
Anonymous
Thanks for the help so far but i still get an error.
Function 'SWITCH' does not support comparing values of type True/False with values of type Tekst. Consider using the VALUE or FORMAT function to convert one of the values
I removed the ) in the formula except in the beginning and the end, but it still doesn't work. Im sure it's just a minor change.
StageName2 = Switch(Project[StageName] = "Suspect" ; "1.Suspect";
"Prospect"; "2.Prospect";
"RFI received (Qualify)"; "3.RFI received (Qualify)";
"Qualified"; "4.Qualified";
"RFQ received"; "5.RFQ received";
"Bid Made"; "6 Bid Made";
"Order"; "7.Order";
"Lost Order"; "8.Lost Order";
"No Bid"; "9.No Bid";
"Project Withdrawn"; "10.Project Withdrawn";
"No stageName")Your first argument to SWITCH is what will be evaluated. You should ONLY use Project[StageName].
Then your values is what will be compared to your first argument. So in your code remove = "Suspect" and instead start the formula like this:
StageName2 = Switch(Project[StageName];
"Suspect";"1.Suspect";
...
...