Modify Cell Value Based on Update in another cell

[Chris_Herrmann]

I have a table with project status, proposal amounts, and percent likelihood of receiving the project. I’d like to have the percent likelihood automatically update to 100% when the project status goes from pending to awarded.

Is this possible to achieve?

Thanks!

[Noel_Howell1]

You could make the % a formula field and nest a bunch of IF() statement within it-- so that if the status = Awarded, then percentage = 100. You’ll have to set up all the other status %s inside the statement too.

[Julian_Kirkness]

Hi Chris

Noel’s solution will certainly work for you as long as you don’t need to manually alter the Percentage (because it’s now a Formula field). The are some choices you could make here:

The simplest would be a slight modification of the above - have a calculated % Chance field which uses a formula to take the manually entered % unless the Status = Awarded:

Another option, which I’ve often used in CRM type applications, is to have a table containing your Sales Statuses and for this table to hold a % Chance of winning associated with each Status. This approach kind of normalises your forecasting of your sales:

Then in this case, the % on your forecasting project table is actually a lookup from the Sales Statuses table:

I hope this helps!

[Robin_Travis]

Hi Chris

Thought I would add my experiences. Both methods that Julian suggests work and I have used them. One problem with the lookup is that if you have colour coded your status in the lookup table, it doesn’t transfer to the referencing table {- @Katherine_Duh : this would be a useful facility as it saves nested IF()s - } For instance; if you use green to show that you have won the project, the referencing table won’t show this. Therefore, you have to use a series of nested If() statements which is a pig to debug if you add a space or don’t match the number of brackets. Should you decide to go down the IF() route, then I would also strongly recommend a drop down list for the project status, so that those entering the data are not in danger of mistyping the status.