Hi Tania,

The formula has too many parentheses, but that won't be causing the problem. Here's the same formula without the extra parentheses:

=IF(P6>0,
IF(OR(C6="Drawing",C6="Document"),CONCAT(A6,"_","r",Q6,R6),
IF(AND(C6="Datasheet", P6<0), L6," "))," ")

If it's not returning L6 then C6 either doesn't contain the text, Datasheet, or P6 is not greater than zero.

Hard to say more without seeing the file or a sample file.

Mynda

Hi Mynda

yes I thought that too... that P6 is not empty, even though it appears to be empty.
see attached sample file.

on sheet "CPB Doc Request" in column C on the yellow rows is where I am testing this formula. row 6 to be exact.

so in the drop down of C6, if it = "Documents" or "Drawings" then I need it to return the CONCAT(A6,"_","r",Q6,R6) but only if there is data in P6. perhaps P6>0 is not the correct formula to use here?

but... if C6 = "Datasheets" I want it to return the value in L6, AND I need to make sure there is NO data value in P6, but if there is accidently data in P6, then it could return a false "Please remove data in column P" or something along those lines.

Hope that makes sense.

Tania

Hi Tania,

In the file you shared cell P6 is empty, and the first logical test of the formula is to test if P6>0. In your file P6 is not greater than zero, therefore it skips to the 'value if false' argument, which is " " and returns a space. 

If you enter a value in P6 the formula evaluates as you wish.

Mynda

ahh ok thanks

but there needs to be no value in P6 for this one. 

how do I write the formula. for the following.

If C6 = Drawing or Document with a value in P6, then it returns the concate formula

But if C6 = Datasheet, with no Value in in P6 then it just needs to return L6. 

sorry but I am a bit stumped onto how to write the correct formula for this.

Kind Regards

Hi Tania,

Try this:

=IF(AND(C6="Datasheet",P6=""),L6,IF(P6<>"",IF(OR(C6="Drawing",C6="Document"),CONCAT(A6,"_","r",Q6,R6),""),""))

Mynda

thanks Mynda, this worked perfectly

thanks so much for your help.