Data Validation & Lists Combined

Question

I have two named groups of data lists and I want to combine them into one list in the data validation window... one data group list is named "Substrates" and the second data group list is named, "Products."

In the data validation window, I have the drop down for "Allow=data list" and next I want to enter into the "Source" input space, the following:

Source=Substrates&Products

But Excel won't accept that syntax... what would be the right syntax for this?

Thanks!

Answer 1

Hi,

a data validation list is either in One Column eg A1:A10

or in One Row eg A1:K1

so, you can't 'add'  two separate lists

Answer 2

Hi,

assuming that you want to merge two separate

sections

A1:D1 and A10:A20

you will need a helper column say, W

in W1 write

=OFFSET($A$1,COLUMNS($A:A)-1,ROWS($1:1)-1)

and drag down up to W4

in cell W5 write

=A10

and drag down

now,

if you need define name

select data in W1:W15,...ect

next ...

Data > Validation > Allow > List ... Source >....

Answer 3

Apostolo55,

    I would be interested in your approach for "appending" lists.

Thanks!

Answer 4

hi, can you use an intermediate column somewhere within your workbook? There is a way to "append" arrays but the result cannot be accepted in DataValidation, so you have to project the lists somewhere and let the DataValidation read them from there. Of-course the whole process is fully automatic, all is needed is a projection column with enough free columns to keep both lists (maybe hidden or in another sheet).

If yes then let me know and I will show you how...

Answer 5

Hi TasosK,

My two lists are in fact both single columns, I was just hoping to the two column lists could be combined together in the data validation list/dropdowns. Guess there is no way to do that... I'll need to come up with another solution.

Thanks!