If then

[Jim]

Hello,

I would like to ask for help with a formula. I report onto a second sheet
the referenced cell unless it meets one of the following criteria. For
example:

If "Sales_Opportunity_Input!CR1" = OPPown then Opportunity Owner, or if
"Sales_Opportunity_Input!CR1" = close date then Close Date, or if
"Sales_Opportunity_Input!CR1" = start then Comm. Start Date, or if
"Sales_Opportunity_Input!CR1" = end, then Comm. End Date

but if non of the creiteria are met, then show the data in
Sales_Opportunity_Input!

Thanks in advance for the help. I appreciate the time.
Jim

 

[Roger Govier]

Hi Jim

To keep the formula shorter and easier to maintain, I would go to
"Sales_Opportunity_Input!CR1" and in the Name Boix (to the left of A and
above row 1) type myCell and hit Enter. This will give it a name.

The your formula is a simple nested IF statement.

=IF(myCell="Oppown","Opportunity Owner",
IF(myCell="Close date","Close Date",
IF(myCell="Start","Comm. Start Date",
IF(myCell="end","Comm. End Date",""))))

The above should all appear on one line, i have split it to avoid the
newsreader breaking it in odd places.

I didn't understand your last part, so I have just left the cell blank
in the event of no condition being met.

 

[Paul C]

Just nest your conditions as follows

If (Sales_Opportunity_Input!CR1 = OPPown, OPPown, if
(Sales_Opportunity_Input!CR1 = close date, close date, if(
Sales_Opportunity_Input!CR1 = start , start , if(
Sales_Opportunity_Input!CR1 = end, end, Sales_Opportunity_Input!CR1 ))))

If the variables you are referenecing are not range name just subsititue the
cell refereneces

=IF(Sales_Opportunity_Input!CR1=A1,A1,IF(Sales_Opportunity_Input!CR1=A2,A2,IF(Sales_Opportunity_Input!CR1=A3,A3,IF(Sales_Opportunity_Input!CR1=A4,A4,E1))))