Need better solution for Data Validation formula

Hi -

I can think of a couple things:

1. If all of your choices begin with "ABG..." why don't you just search on that instead of hard coding all the different possibilities in your formula? So, your formula could look like this:

IF(B17="ABG*","N/A", 'Data Access'!$A$3:$A$5)

2. Suppose you have other ABG categories you don't want to match, another alternative would be to put each of your categories into cells and reference those, rather than hardcoding all the text into your formula. For example, if "ABG Buyer" were in cell J1, "ABG Advanced Buyer" in J2, etc. Your formula could be consolidated to:

IF(AND(B17=J1, B17=J2,B17=J3,B17=J4,B17=J5,B17=J6,B17=J7,B17=J8,"N/A", 'Data Access'!$A$3:$A$5)

3. Taking option 2 a step further, you could use and ARRAY formula to check that range of values; J1 through J8. That would look like:

{=IF(SUM(B17=J1:J8),"N/A", 'Data Access'!$A$3:$A$5)}

Notice the curly braces are added by Excel when you press ctrl-shift-enter from the edit window of the formula.

Hope that helps.

You may put un-wanted values in a range ie. (X1:X10), and put "N/A" in Y1
then use this formula in data validation list.

=IF(COUNTIF(X1:X10,B17)>0,Y1,'Data Access'!$A$3:$A$5)

Regards.

Hi ,

Another option is to create a table , containing in the first column the category labels , and in the second column have a numeric value from 1 to the number of categories / labels which can be entered in B17.

Now , the formula becomes a simple :

Formula:

Please Login or Register  to view this content.

You can add as many categories as you want to the IF statement , by merely adding them to the table from row 10 downwards , and changing the 8 in the above formula to the new number. Thus , if you add another 5 categories , change the number 8 to 13. That is all.

See the attached file for the table.

In fact , you can add all the possible categories that can be entered in cell B17 to this table , and then use the table column Category for data validation in cell B17.

Narayan

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)