Correct syntax for a formula

Hi there,

I need the correct syntax for a formula that will give me a 'True' from one of 5 'what if' conditions.

Okay. In sheet1 we have an index number in A1, let's give it ABC0001. In B1 we have a drop down list presenting 5 options; 'open', 'closed', 'on-hold', 'closed - awaiting adjustment', and 'awaiting response'.

On sheet2, I want the result of the argument (if true) to appear at A1. The result of the argument will be whatever is on sheet1 at A1, which in this case is the index number ABC0001. No matter which of the 5 conditions appears on sheet1 at b1, the result appearing on sheet2 at A1 will always be whatever is in A1 on sheet 1.

I have the first bit working...that is to say, the appearance of one condition only...thus: =IF('sheet1'!B2<>"OPEN",'sheet1'!A2,""). Of course, I need the other 4 arguments included in the formula. Ultimately, whichever condition from the dropdown box appears at sheet1 in B1. The result will be whatever index number appears on sheet1 at A1, will appear on sheet2 at A1. If no condition has been chosen, and sheet1 B1 is blank, then A1 on sheet2 will also be blank.

I need to 'nest' the other conditions of the dropdown box in the same formula, but to give only one and the same result.

Can any kind person help me?

Kindest regards to all

Aristillus

Hi Aristillus,
Your explanation uses A1 and B1 but the formula you show uses A2 and B2.
keeping with your example formula and correcting TMS (sorry TMS)

Formula:

Please Login or Register  to view this content.

Change the A2 B2 to A1 B1 if that is what you need.

Thank you for the replies guys.

TMS, your formula works rather well, and does what I require. Many thanks for the truncated and more elegant formula. I don't know why I was thinking I needed to nest the conditions.

Caveman1957,

I should have made it more clear that the formula was going into Sheet2 A1. Thanks for the help.

Regards all

Aristillus