Help with nested IF and CONCATENATE functions
How do I create a formula to do the following:
If C3="Flour" and B6="all purpose", "strong bakers" or "pastry" then string the following text into one cell: Flour+B6+H11+D13+J11+G11+F11+D14+M11+B11+K11+L11+C11 OTHERWISE, string the following text into one cell: Flour+Flour+H11+D13+J11+G11+F11+D14+M11+B11+K11+B6+L11+C11
The difference between the true and false is where cell B6 is located in the text string; so in the first condition B6 is after Flour, in the second condition it is closer to the end.
Here is what I've come up with so far. It does work, however the B6 does not move, it stays after "Flour" even when the conditions are false.
=IF(OR($C$3="Flour", B$6="all purpose",$B$6="strong bakers",$B$6="pastry"),"Flour"&" "&B6&" "&H11&" "&D13&" "&J11&" "&G11&" "&F11&" "&D14&" "&M11&" "&B11&" "&K11&" "&L11&" "&C11, "Flour"&" " &H11&" "&D13&" "&J11&" "&G11&" "&F11&" "&D14&" "&M11&" "&B11&" "&K11&" "&B6&" "&L11&" "&C11)
I don't know how nor do I want to use VBA. Can anyone help me with this one?
Thank you in advance!
You're missing an AND function in there
Try the following:
=IF(AND(OR(B$6="all purpose",$B$6="strong bakers",$B$6="pastry"),$C$3="Flour"),"Flour"&" "&B6&" "&H11&" "&D13&" "&J11&" "&G11&" "&F11&" "&D14&" "&M11&" "&B11&" "&K11&" "&L11&" "&C11, "Flour"&" " &H11&" "&D13&" "&J11&" "&G11&" "&F11&" "&D14&" "&M11&" "&B11&" "&K11&" "&B6&" "&L11&" "&C11)
Regards
Mark