Validation two lists into one

[raccoon588]

I am trying to create a drop down list using Validation. I have a list of names on SSLFirst and on AuxFirst. I need to be able to combine these list so that they both show up in one drop down list in the cell. I can not seem to get them to come together. is it possible. i tried using offset but i get an error.

 

[Chrisdontm]

This might be a lengthy answer, but it will get you what you want...

Imagine if you had one list starting in E3 and going on down, and nothing else is in that column,
And you had a second list starting in G3 and going on down, and nothing else is in that column...

I would combine them into one column and make them dynamic so that the lists could grow and shrink.
( Change to your ranges accordingly )

Put this formula into M3, and copy it on down to row 50. ( This range is for my example ).

It will copy over one list, and when that list runs out it will copy over the next list;

=IF(COUNTA($E:$E,$G:$G)<=COUNTA($M$1:$M2),"",IF(OFFSET($A$1,ROWS($A$1:$A1)+1,4,1,1)=0,OFFSET($A$1,COUNTA($M$1:M2)-COUNTA($E:$E)+2,6,1,1),OFFSET($A$1,ROWS($A$1:$A1)+1,4,1,1)))

Then go to Data Validation in the Data tab, choose " List ", and then for the " source " put this formula into it;

=OFFSET($A$1,2,12,ROWS($M3:$M$50)-COUNTBLANK($M3:$M$50),1)

 

[raccoon588]

My two lists are on two separate tabs in the excel file. they would need to stay separate.

 

[oldbrewer]

leeks
			apples		potatoes		apples	1565759	1565759	apples		a	1000000		a	1		1
			pears		beans		pears	16286876	2004024	beans		b	2000000		b	27		2
			bananas		peas		bananas	2198181	2198181	bananas		c	3000000		c	270		3
					onions			999999999	12136539	leeks		d	4000000		d	1323		4
					leeks			999999999	15489267	onions		e	5000000		e	3996		5
								999999999	16286876	pears		f	6000000		f	10449		6
								999999999	16286876	pears		g	7000000		g	20682		7
								999999999	17019196	potatoes		h	8000000		h	34695		8
								999999999	999999999			I	9000000		I	52488		9
								999999999	999999999			j	10000000		j	74061		10
							potatoes	17019196	999999999			k	11000000		k	99414		11
							beans	2004024	999999999			l	12000000		l	128547		12
							peas	16286876	999999999			m	13000000		m	161460		13
							onions	15489267	999999999			n	14000000		n	198153		14
							leeks	12136539	999999999			o	15000000		o	238626		15
								999999999	999999999			p	16000000		p	282879		16
								999999999	999999999			q	17000000		q	330912		17
								999999999	999999999			r	18000000		r	382725		18
								999999999	999999999			s	19000000		s	438318		19
								999999999	999999999			t	20000000		t	497691		20
												u	21000000		u	560844
												v	22000000		v	627777
			col D		col F		col H	col I	col J	col K		w	23000000		w	698490
												x	24000000		x	772983
												y	25000000		y	851256
												z	26000000		z	933309
	each of my 2 lists in col D and col F are 10 rows deep
	ie each contain blanks
	col H is second list added to first list
	ie 20 rows deep
	using 2 lookup tables
	I assign UNIQUE values to the first 3 letters
	of each fruit or vegetable
	giving them a unique code
	now column J is the smallest, then the next smallest etc
	and finally column K pulls the food name across
	so K2:K21 is named mydropdown
	and A1 is the dropdown with the 8 food names (the blanks are invisible at the bottom

<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col span="2"><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Chrisdontm]

( Change to your ranges accordingly )

I put that statement in intending for you to replace my formula ranges to your ranges.
If it is different tabs just put that within the formula...