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!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

PCL

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

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

PCL

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

ADVERTISEMENT

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
58,661
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,348
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,133,811
Messages
5,661,044
Members
418,612
Latest member
iihtsurat5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top