Combine lists

**rderkins** · 04-25-2010, 04:10 PM

I have two separate lists in a workbook that are used to validate data for various fields. I have one field that needs to be a drop-down containing the combined data from both of these lists.

I am trying to figure out how to do one of the following:

1) Use validate to allow data that is contained in either of two lists without combining the lists into a single list (don't know if this is possible)

2) Make a third list that contains the data from each of the initial two lists but which does not have to be maintained separately (i.e. if I add or remove data from list 1 or list 2, the appropriate change is made in list 3 as well).

Any ideas?

**Palmetto** · 04-25-2010, 05:56 PM

Any ideas?

JBeaucaire, another guru on this forum, has developed an excellent approach that may work for you. See this link and see if it can be adapted per your needs.

Auto Complete in a Data Validation cell technique

**JBeaucaire** · 04-25-2010, 09:59 PM

If List 1 is in column A starting at row 2
If List 2 is in column B starting at row 2
To have List 3 create itself in column B starting at C2, put this formula in C2 and copy down as far as you'd like:

=IF(ROW(A1)<=COUNTA($A$2:$A$100), INDEX($A$2:$A$100, ROW(A1)), IF(ROW(A1)<=COUNTA($A$2:$A$100)+COUNTA($B$2:$B$100), INDEX($B$2:$B$100, ROW(A1)-COUNTA($A$2:$A$100)), ""))

Then use this formula to create a dynamic named range called MergedList from the changing values of column C:

=OFFSET(Sheet2!$C$2,0,0,MATCH("*",Sheet2!$C:$C,-1)-1,1)

Then use that named range in you drop down validation. Sample attached.

Combine lists

LinkBack

Thread Tools

Rate This Thread

Display

Combine lists

Re: Combine lists

Re: Combine lists

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions