Help with making formula smaller

Dear Excel Forum Users,

I am attempting to write a formula in the Source bar found in the Data Validation box to create a scenario where a list will be visible based on the formula i have written. The issue that I ran into is that in the source bar in the Data Validation box I can't type anymore characters it just gives that error sound. Now I have pasted my formula below with the hope that someone could help me slim down the formula so I could enter it into the source bar. I will show both the full formula I wrote and the point in which the source bar stopped me from typing further so you could understand how much smaller I need the formula to be. Thanks in advanced.

-Avidan


Just a side note the Data Access part found at the end is pulling the false data from another tab where if the statement above is false it displays the list otherwise as shown above it will show "N/A".

Full Formula: =IF(OR(AND(A17="Purchasing",B17="ABG Buyer"), AND(A17="Purchasing",B17="ABG Advanced Buyer - Admin Only") ,AND(A17="Purchasing",B17="ABG Advanced Procurement Requestor"),
AND(A17="Purchasing",B17="ABG Procurement Catalog Administrator")),"N/A",'Data Access'!$A$3:$A$6)

Formula was cut off at: =IF(OR(AND(A17="Purchasing",B17="ABG Buyer"), AND(A17="Purchasing",B17="ABG Advanced Buyer - Admin Only") ,AND(A17="Purchasing",B17="ABG Advanced Procurement Requestor"),
AND(A17="Purchasing",B17="ABG Procurement Catalog Administrator")),

Hi ,

The Data Validation formula length is 255 , which is why your formula is being truncated at that point.

See if this formula works the same way :

Formula:

Please Login or Register  to view this content.

Narayan

Hi AvidanR,

If you could reduce the size of the job titles in column B that should work. If you don't want to amend the source data create a helper column, a separate table that contains the long and short names and use VLOOKUP to pull in the shorter name. Then use this as the equivalent criteria for B17 and amend your formula accordingly.

Hope this helps.

Regards,

Snook

You can slim it down like
=IF(AND(A17="Purchasing",OR(B17="ABG Buyer",B17="ABG Advanced Buyer - Admin Only",B17="ABG Advanced Procurement Requestor",B17="ABG Procurement Catalog Administrator")),"N/A",'Data Access'!$A$3:$A$6)
But you will probably need to supply a list instead of N/A for the true part

Dear Narayan,

Thank you so much it works!!!!!

-Avidan