Convert 2 lists into 1

Hello all,

I need to create a name range that is the total of 2 name range.

I can also create a new column with the total lists, but i want it to be flexible cuz i will insert several more items to lists. Hope you can convert this.

What i have is: in (sheet "login")
Column E Column Z
Username Username
A D
B E
C

I need to have another list called: in (sheet "Validações")
Users
A
B
C
D
E

Once in a while i increase users so i need this to be automatic in order not to forget.

Hope some one can help me

The atual range adress are in login sheet
G8:G20 but some are empty for new users
And T8:T20 in login sheet as well.

I want to put the combine list in worksheet("validações"). Range("L2:L")

Hope you can understand me

Originally Posted by Tony Valko

Are the empty cells scattered within the range or are the empty cells at the bottom of the range?

Sorry for late reply. The empty cells are always at the Bottom of the range.
Which means that once we hit empty we can go to next range.

Hope to hear from you

Originally Posted by Tony Valko

Create these dynamic named ranges.

Name: List1
Refers to:
=Login!$G$8:INDEX(Login!$G$8:$G$100,MATCH("zzzzz",Login!$G$8:$G$100))

Name: List2
Refers to:
=Login!$T$8:INDEX(Login!$T$8:$T$100,MATCH("zzzzz",Login!$T$8:$T$100))

Then, enter this formula in cell L2 on the Validações sheet:

=IFERROR(IF(ROWS(L$2:L2)>ROWS(List1),INDEX(List2,ROWS(L$2:L2)-ROWS(List1)),INDEX(List1,ROWS(L$2:L2))),"")

Copy down until you get blanks.

In the formulas you may have to replace the commas with semi-colons.

All your job worked perfectly, just had to convert to portuguese Excel the formula and switch , to ;
Although now i need to get a drop down list of that ALL users RANGE, but since there is a formula, it appears in blank several values