[HELP] How do I create a list (Data Validation) and add to the list without cell reference

Hi,

You can't use the List feature of Data Validation with non-contiguous ranges, nor with a mixture of range references and delimited text values.

You have a bit more flexibility in restricting the entries for users, as follows:

For your first question, change your Data Validation from 'List' to 'Custom' and enter this formula:

=OR(ISNUMBER(MATCH($B$3,$G$3:$I$3,0)),$B$3="Seven")

Similarly for your second question: enter the custom Data Validation formula:

=OR(ISNUMBER(MATCH($B$3,$G$3:$I$3,0)),$B$3=$G$13)

And if you want all these combined, your formula would be:

=OR(ISNUMBER(MATCH($B$3,$G$3:$I$3,0)),$B$3=$G$13,$B$3="Seven")

However, although these solutions will validate your cells accordingly, they will not produce a drop-down list for the user. For these, I'm afraid you will have to either arrange your data in a single row or column or enter all options as text under the List option of Data Validation.

Regards

Why can't you create a list for your Data Validation on e.g. another tab? You could even hide this tab if you really wanted. So on sheet2 in cells A1:A5 you could have One, Two, Three, Four, Seven.

And if you wanted to retain the dynamism of linking entries in this list to Sheet1, then in Sheet2:

In A1 type this formula: =Sheet1!G3
In A2: =Sheet1!H3
In A3: =Sheet1!I3
In A4: =Sheet1!G13
In A5: Seven

Then your Data Validation List is simply: =Sheet2!$A$1:$A$5

Regards