Not seeing the syntax error

TWienholz

Board Regular
Joined
Aug 3, 2016
Messages
50
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

Well-known Member
Joined
Jul 15, 2008
Messages
1,345
It looks loke the AND has only 1 argument

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

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,345
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

Board Regular
Joined
Aug 3, 2016
Messages
50
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

Well-known Member
Joined
Jul 15, 2008
Messages
1,345
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 ")"
 
Last edited:

TWienholz

Board Regular
Joined
Aug 3, 2016
Messages
50
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,397
Office Version
365
Platform
Windows
How about
Code:
=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

Well-known Member
Joined
Jul 15, 2008
Messages
1,345
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

Board Regular
Joined
Aug 3, 2016
Messages
50
How about
Code:
=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! :)
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top