Combining Two Range of Entries into One

J

JaGGeR

Hiya all..

I'm hoping that somebody could help me out. I have two sets o
entries:

Set 1 ( A1:A10 )
item 1
item 2
item 3

Set 2 ( B1:B10 )
data 1
data 2
data 3
data 4

What I am trying to do is to have a certain cell (say C1) have
dropdown list containing entries from both sets. The formula for th
valid data input would be similar to:

offset(a1,0,0,counta(a:a),1) + offset(b1,0,0,counta(b:b),1)

So far, I have no luck in getting this to work. Any suggestion o
alternative approach to this would be very much appreciated.

:
 
B

Biff

Hi JaGGer,

An alternative approach would be to concatenate the two
ranges and put that list somewhere out of sight and create
the dropdown from the new list.

For example in AA1, formula =A1&B1. Then use AA1 as the
source for the dropdown.

Biff
 
J

JaGGeR

I believe AA1 would result only to an entry of "item 1data 1" and the
dropdown list with that one entry only.

I was hoping to achieve a dropdown list of:
item 1
item 2
item 3
data 1
data 2
data 3
data 4

Furthermore, if the entries in Set 1 and Set 2 were changed, the
dropdown list would dynamically change as well. For example:

Set 1 becomes:
item 1
item 2

Set 2 becomes:
data 1
data 5
data 7

Resulting dropdown list becomes:
item 1
item 2
data 1
data 5
data 7
 
L

Leo Heuser

Hi

Here's one way to accomplish it:

In e.g. K1 enter the formula:

=IF(ROW()-ROW($K$1)<COUNTA(A:A),OFFSET($A$1,ROW()-
ROW($A$1),0),IF(ROW()-ROW($K$1)<COUNTA(A:A)+
COUNTA(B:B),OFFSET($B$1,ROW()-ROW($B$1)-COUNTA(A:A),0),""))

Drag K1 down with the fill handle to K20.

K1:K20 will now contain Set1 + Set2 + a number of cells with "".
The (Set1 + Set2)-part has COUNTA(A:A)+COUNTA(B:B) cells.

To get the range for the drop down box use:

=OFFSET(K1,0,0,COUNTA(A:A)+COUNTA(B:B))
 
L

Leo Heuser

Addendum to clarify:

Leo Heuser said:
Hi

Here's one way to accomplish it:

In e.g. K1 enter the formula:

=IF(ROW()-ROW($K$1)<COUNTA(A:A),OFFSET($A$1,ROW()-
ROW($A$1),0),IF(ROW()-ROW($K$1)<COUNTA(A:A)+
COUNTA(B:B),OFFSET($B$1,ROW()-ROW($B$1)-COUNTA(A:A),0),""))

Drag K1 down with the fill handle to K20.

K1:K20 will now contain Set1 + Set2 + a number of cells with "".
The (Set1 + Set2)-part has COUNTA(A:A)+COUNTA(B:B) cells.
Click to expand...

1. Choose Insert > Name > Define
2. Name: DropDownRange
3. Refers to: =OFFSET($K$1,0,0,COUNTA($A:$A)+COUNTA($B:$B))


To get the inputrange for the drop down box, set its
inputrange to DropDownRange.
 
Top