Log In
Register
Home
December 7, 2020
Offline
HI
I have the below nested IF, OR and AND function, it works except for the last one L6, if C6 = "Datasheet" then I want it to show the value in L6, which it is not doing.
where am I going wrong please????
=IF(P6>0,(IF(((OR(C6="Drawing",C6="Document"))),CONCAT(A6,"_","r",Q6,R6),
IF((AND(C6="Datasheet", P6<0)), L6," ")))," ")
thanks
See Answer
July 16, 2010
Offline
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
December 7, 2020
Offline
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
July 16, 2010
Offline
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
December 7, 2020
Offline
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
1 Guest(s)
