Search code examples
excelvalidationexcel-formulaformula

Conditional Data Validation - Returning a list of entries based on availability

In **column A, **I have a list of names, and in **column B, **a list of nicknames. Not every name will have a corresponding nickname.

names and nicknames

The following formula generates a list of the names in column A while substituting nicknames from column B in every case where there is one:

=IF(ISBLANK($B$2:$B$8), $A$2:$A$8, $B$2:$B$8)

However, when I use this formula to generate a list for data validation, it produces an error. Is it possible, (preferably without VBA), to recreate this functionality in a format that data validation will accept? I've had trouble "phrasing" this issue in Google and haven't found a solution.

I've tried using the previous formula in the data validation, but it results in an error:

The Source currently evaluates to an error. Do you wish to continue?

I've also tried concatenating the results of the formula into a comma-delimited string, but it looks like data validation won't parse the string after parsing the formula and results in the entire string being the only validated option.

Solution

  • What you are asking actually IS possible.

    By using the 'custom' criteria in the data validation window, you can write a formula that tests if the entered argument is part of the list.

    However, the 'custom' formula cannot simply be a list itself, but has to return a TRUE or FALSE value directly.

    For example, as shown in this screenshot:

    example

    The formula used is:

    =OR(G3=IF(ISBLANK($B$2:$B$9),$A$2:$A$9,$B$2:$B$9))

    So this formula test if any items of the list that you specified through the IF function is equal to G3, the cell on which the data validation itself is applied.

    Moreover, if you have a whole range that shall have this type of data validation, you do not have to rewrite the data validation for every cell. By using a relative reference G3 (instead of absolute reference $G$3) the data validation will automatically apply correctly to each cell of the range