Not seeing the syntax error

[TWienholz]

I have the following formula that is giving me a #VALUE ! error and I've been looking at it so long I can't see where the syntax is wrong. Can someone please help?

=IF(AND(Y17="Replacement2",Z8<3),"Wave "&'Project Data Sheet'!J73&" Print Order","Wave "&'Project Data Sheet'!B101&" Print Order"),IF(AND(Y17="**REPRINT**",Z8<3),"Wave "&'Project Data Sheet'!J73&" Reprint Order","Wave "&'Project Data Sheet'!B101&" Reprint Order")


Basically what I'm asking it to do is title the report based on the Wave information (<3=Wave B, >2=Wave C) so that if the APM is requesting a Replacement2 print where the project only has 2 waves then the end result would read "Wave B Print Order". If it was a 3 wave project the end result would read "Wave C Print Order". The first part of the formula works by itself and returns the correct title if I remove the second IF condition. However, when I add the second condition, but with the APM requesting a **REPRINT**, I get the value error.

I'm almost positive that there is an open or closed parenthesis or a comma or something either missing or in the wrong place here and I'm not seeing it. Any suggestions would be VERY much appreciated!

Thanks so much!

 

[PCL]

It looks loke the AND has only 1 argument

AND( Y17="Replacement2",Z8<3)

 

[PCL]

The first IF finish here

IF( AND( Y17="Replacement2",Z8<3) ,"Wave " & 'Project Data Sheet'!J73 & " Print Order" , "Wave " & 'Project Data Sheet'!B101 & " Print Order")

See how are the commas

 

[TWienholz]

The first IF finish here

IF( AND( Y17="Replacement2",Z8<3) ,"Wave " & 'Project Data Sheet'!J73 & " Print Order" , "Wave " & 'Project Data Sheet'!B101 & " Print Order")

See how are the commas

But aren't those 2 arguments? If it says Replacement2 and it's less than 3 waves? Then return either Wave B Print order, or Wave C Print Order. That part works on its own. It gets messed up when I add the second part of the formulat pertaining to the REPRINT request.

 

[PCL]

About the AND you're right I missed a comma but concerning the first IF it stops like that

IF( AND( Y17="Replacement2", Z8<3) , "Wave " & 'Project Data Sheet'!J73 & " Print Order" , "Wave " & 'Project Data Sheet'!B101 & " Print Order" )

The second IF is not involved
You could count the amount of "(" versus ")"

 

[TWienholz]

About the AND you're right I missed a comma but concerning the first IF it stops like that

The second IF is not involved
You could count the amount of "(" versus ")"

But what I'm saying is the first part of it works exactly the way you see it there....namely,

=IF(AND(Y17="Replacement2",Z8<3),"Wave "&'Project Data Sheet'!J73&" Print Order","Wave "&'Project Data Sheet'!B101&" Print Order") ----> Wave B Print Order (there are 2 waves in my example; it also works if I change the number of waves to 3 and returns Wave C print order)

However, when I add the second condition there seems to be some conflict with the first which is causing the #VALUE ! error, but I'm not sure how to change the second condition to make it work as it did in the first instance (all I did was copy the first condition and change one part of the criteria).

=IF(AND(Y17="Replacement2",Z8<3),"Wave "&'Project Data Sheet'!J73&" Print Order","Wave "&'Project Data Sheet'!B101&" Print Order"),IF(AND(Y17="**REPRINT**",Z8<3),"Wave "&'Project Data Sheet'!J73&" Reprint Order","Wave "&'Project Data Sheet'!B101&" Reprint Order") ----> #VALUE !

 

[Fluff]

How about

=IF(Y17="Replacement2",IF(Z8<3,"Wave "&'Project Data Sheet'!J73&" Print Order","Wave "&'Project Data Sheet'!B101&" Print Order"),IF(Y17="**REPRINT**",IF(Z8<3,"Wave "&'Project Data Sheet'!J73&" Reprint Order","Wave "&'Project Data Sheet'!B101&" Reprint Order"),"?"))

 

[PCL]

when I add the second condition there seems to be some conflict with the first

Yes , you must not add but include the second condition inside the first one

 

[TWienholz]

How about

=IF(Y17="Replacement2",IF(Z8<3,"Wave "&'Project Data Sheet'!J73&" Print Order","Wave "&'Project Data Sheet'!B101&" Print Order"),IF(Y17="**REPRINT**",IF(Z8<3,"Wave "&'Project Data Sheet'!J73&" Reprint Order","Wave "&'Project Data Sheet'!B101&" Reprint Order"),"?"))

THANK YOU SO MUCH!! This worked like a charm!

 

[TWienholz]

Thank you so much for your help and your suggestion!