PDA

View Full Version : [SOLVED] CONCATENATE used with IF AND funtion



simora
10-17-2014, 01:34 PM
In Cell D2 I am using this formula =CONCATENATE(BookNumber1, " ","","-","",C2)


BookNumber1 is a single cell Named Range


How do I modify it so that If C2 >0 & E2>0 then trigger the CONCATENATE function, else return nothing ("") I HAVE TO FILL THIS FORMULA DOWN THE PAGE TO ROW 366


cANT UNDERSTAND WHY THIS IS NOT WORKING. hOW DO i CORRECT THIS.?


=IF(AND(C2>0,E2>0,CONCATENATE(BookNumber1, " ","","-","",C2)),” ”)

SamT
10-17-2014, 02:04 PM
Parens in wrong places.

In the following, if C2 = 42, and Booknumber = "The Answer is", then

=IF(AND(C2>0,E2>0),CONCATENATE(BookNumber1, " - ",C2),””)

Returns: "The Answer Is - 42"

Note spaces in Return and in the Concatenate function

edited for ted: removed extra )

Aussiebear
10-17-2014, 06:56 PM
Sorry Sam, but I've stuffed up your answer from too little sleep. My most humble apologies.

One too many ) in there Sam. Try the following
=If(And(C2>0,E2>0),CONCATENATE(BookNumber1, " - ",),"")

There, I fixed it
SamT LOL

Aussiebear
10-17-2014, 09:14 PM
Thankfully one of us is on the ball.

simora
10-17-2014, 09:29 PM
Aussiebear (http://www.vbaexpress.com/forum/member.php?3907-Aussiebear) :

That's it. I somehow also missed that final C2

=IF(AND(C2>0,E2>0),CONCATENATE(BookNumber1, " -",C2),"")

SamT (http://www.vbaexpress.com/forum/member.php?6494-SamT) : Got it.

Thanks all.

snb
10-18-2014, 04:35 AM
Alternative:

=IF((C2>0)*(E2>0),"",BookNumber1&" -"&C2))