Not seeing the syntax error

TWienholz

Board Regular
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
It looks loke the AND has only 1 argument

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

PCL

Well-known Member
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
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

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
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

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
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
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!

Replies
12
Views
167
Replies
4
Views
224
Replies
13
Views
706
Replies
0
Views
273
Replies
0
Views
1K

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.

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

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