more arguments have been specified error on IF statement

[phoj69]

I am very much a beginner when it comes to excel so this might not even be the correct way to go but I have a file currently that filters out locations that I do not want to see. So for instance I have a part #, Qty, Location columns and a formula off to the side that says if the location equals and one of these locations to filter it out but I have come across som many locations I am getting an error. Here is the formula below is there a way I can do this without erroring out? I need to add more locations but it won't let me. thank you


=IF(OR(O6="VENDOR",O6="PURGE1",O6="SHPING",O6="SGI1",O6="P2RUBA",O6="WTGRTV",O6="SECRET",O6="01",O6="0000",O6="GPMRB",O6="CXP",O6="CXP1",O6="MEL",O6="P2MAZZ",O6="P2LOVE",O6="P2MELE",O6="00",O6="0",O6="P2JOHA",O6="P2MCMA",O6="P2SHAN",O6="P2VING",O6="TRAN",O6="NOFIND",O6="DI1",O6="WK1",O6="UNKW",H6>"4Z",H6<"4A"),"**","")

 

[mole999]

You have lots that start with P2 so this will eliminate 8 in a go

=LEFT(O6,2)="P2"

[what do you think will be you max limit

earlier excel was limited to 7 IF statements, I guess you could cobble together 7 big OR statements, I've also seen somewhere the use of & to join tests (not sure how thats achieved)

I'm sure there are more efficient ways, or maybe a macro

as in

=IF(OR(O6="VENDOR",O6="PURGE1",O6="SHPING",O6="SGI1",left(O6,2)="P2",O6="WTGRTV",O6="SECRET",O6="01",O6="0000",O6="GPMRB",O6="CXP",O6="CXP1",O6="MEL",O6="00",O6="0",O6="TRAN",O6="NOFIND",O6="DI1",O6="WK1",O6="UNKW",H6>"4Z",H6<"4A"),"**","")

 

[AlphaFrog]

It may be easier to have a list of location exclusions in a column (column Z for the example below) and test if O6 matches the list.

=IF(OR(ISUMBER(MATCH(O6,Z:Z,0)),H6>"4Z",H6<"4A"),"**","")

Though I'm not sure what you're trying to accomplish with this...
H6>"4Z",H6<"4A"

 

[RoryA]

You would be much better off listing the values in another range (say Z1:Z50) and then using a simple formula like:
=if(isnumber(match(O6,$Z$1:$Z$50,0)),"**","")

You can shorten your formula using an array though:

=IF(OR(O6={"VENDOR","PURGE1","SHPING","SGI1","P2RUBA","WTGRTV","SECRET","01","0000","GPMRB","CXP","CXP1","MEL","P2MAZZ","P2LOVE","P2MELE","00","0","P2JOHA","P2MCMA","P2SHAN","P2VING","TRAN","NOFIND","DI1","WK1","UNKW"},H6>"4Z",H6<"4A"),"**","")

 

[par60056]

Welcome to the forum. You will find many good answers and a few bad ones.

I'll try to give you good answers but I've been known to be wrong.

According to the help page you should be able to put up to 255 conditions in an OR() So this should not be getting an error for too many conditions. I looked and the quotes and parenthesis line up. It is interesting that most of the tests are against O6 and then you check H6 for a range of values.

I would try to simplify this by making a sheet with the column O values to ignore in column A, name the sheet "ignore"

VENDOR
PURGE1
SHPING
SGI1
P2RUBA

<tbody>
</tbody>

then use the match function to see if it is in the list.

=if(or(not(iserror(MATCH(O6,ignore!A1:A50,0))),H6>"4Z",H6<"4A"),"**","")

 

[par60056]

You have lots that start with P2 so this will eliminate 8 in a go

=LEFT(O6,2)="P2"

This would assume the EVERY value beginning with P2 should be ignored and that may not be the case.

 

[phoj69]

Thank you so much guys I created another sheet with all the locations I wanted to filter out like you suggested and just used your formula listed above. I have one more question that is sort of a continuation to the one above that i think might take a macro but i am not sure. I put an example of the file I am working so what I would like to do is look at the inv locator column and if it had a certain location I would like to copy everything from that line so the ORG, Part #, Sub and inv locator. Now I don't ned it to disappear from the current workbook I just need a copy of that line on another workbook. Hope that made sense.


ORG PART NO SUB INV LOCATOR
XXX 9999999 XXXX 1558-4L-BLDG3-0-

 

[par60056]

you are correct. If you want to do selective copying to another workbook you need to write a macro.

This is a very common question and there are probably 20+ threads that discuss it. Search the forum for copying.

 

[mole999]

This would assume the EVERY value beginning with P2 should be ignored and that may not be the case.

Of course, but then I clarified what was wanted