I have multiple options from a field that are then being converted into a number value. I need there to be a sequential order of starting with one cell and executing an if statement. However, if that first one is not true, then it moves on to the next cell which would have multiple options (of the opportunity stage) that will return a number value for the stage of the opportunity.
To simplify:
If yes in the first cell, moves on to the next cell to determine the stage value of the opportunity. If no in the first cell, then the second level of inquiry is disregarded and a stage value is returned.
What do you mean by โcellโ?
What does โmoves on to the next cellโ mean?
See also:
I may have figured it out by just marrying up the sequences that would most likely be possible. I started with on expression that worked and then trial/error the functions that I needed to insert to make it work with the next option and then on down the line. Iโm not sure if there is an easier or more concise way of formatting the formulation, but this is what it looks like:
=if(OR(AQ43=โResearch reviewโ),"-1",if(OR(BA43=โNoโ),"-99",if(AND(BA43="",AQ43=โInitial research providedโ),โ0โ,if(AND(BA43="",AQ43=โIdentity confirmedโ),โ1โ,if(AND(BA43="",AQ43=โTelephone and/or email contact madeโ),โ2โ,if(AND(BA43="",AQ43=โPersonal meeting/presentationโ),โ3โ,if(AND(BA43="",AQ43=โVerbal acknowledgementโ),โ4โ,if(AND(BA43="",AQ43=โExecuted agreementโ),โ5โ,if(AND(BA43="",AQ43=โOn marketโ),โ6โ,if(AND(BA43="",AQ43=โUnder contractโ),โ7โ,if(AND(BA43="",AQ43=โClosedโ),โ8โ,if(AND(BA43="",AQ43=โActive repeat clientโ),โ9โ,if(AND(BA43="",AQ43=โMulti-transaction clientโ),โ10โ,if(AND(BA43="",AQ43=โResearch reviewโ),"-1",if(AND(BA43=โYesโ,AQ43=โInitial research providedโ),โ0โ,if(AND(BA43=โYesโ,AQ43=โIdentity confirmedโ),โ1โ,if(AND(BA43=โYesโ,AQ43=โTelephone and/or email contact madeโ),โ2โ,if(AND(BA43=โYesโ,AQ43=โPersonal meeting/presentationโ),โ3โ,if(AND(BA43=โYesโ,AQ43=โVerbal acknowledgementโ),โ4โ,if(AND(BA43=โYesโ,AQ43=โExecuted agreementโ),โ5โ,if(AND(BA43=โYesโ,AQ43=โOn marketโ),โ6โ,if(AND(BA43=โYesโ,AQ43=โUnder contractโ),โ7โ,if(AND(BA43=โYesโ,AQ43=โClosedโ),โ8โ,if(AND(BA43=โYesโ,AQ43=โActive repeat clientโ),โ9โ,if(AND(BA43=โYesโ,AQ43=โMulti-transaction clientโ),โ10โ,if(AND(BA43=โYesโ,AQ43=โResearch reviewโ),"-1",if(AND(BA43=โUnknownโ,AQ43=โInitial research providedโ),โ0โ,if(AND(BA43=โUnknownโ,AQ43=โIdentity confirmedโ),โ1โ,if(AND(BA43=โUnknownโ,AQ43=โTelephone and/or email contact madeโ),โ2โ,if(AND(BA43=โUnknownโ,AQ43=โPersonal meeting/presentationโ),โ3โ,if(AND(BA43=โUnknownโ,AQ43=โVerbal acknowledgementโ),โ4โ,if(AND(BA43=โUnknownโ,AQ43=โExecuted agreementโ),โ5โ,if(AND(BA43=โUnknownโ,AQ43=โOn marketโ),โ6โ,if(AND(BA43=โUnknownโ,AQ43=โUnder contractโ),โ7โ,if(AND(BA43=โUnknownโ,AQ43=โClosedโ),โ8โ,if(AND(BA43=โUnknownโ,AQ43=โActive repeat clientโ),โ9โ,if(AND(BA43=โUnknownโ,AQ43=โMulti-transaction clientโ),โ10โ,if(AND(BA43=โUnknownโ,AQ43=โResearch reviewโ),"-1"))))))))))))))))))))))))))))))))))))))
Ah, you need a spreadsheet formula. I donโt help with those.
Yeah, since there is obvious overlap and synergies, I was thinking that someone could easily share the solution. Thanks.
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |