Combining Two Range of Entries into One

  • Thread starter Thread starter JaGGeR
  • Start date Start date
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.

:
 
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
 
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
 
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))
 
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.
 
Back
Top