Convert 2 lists into 1

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

Originally Posted by Tony Valko

Does that mean you want to use the range L2:L? as the source for a drop down list?

If so, use something like this...

=OFFSET(L2,,,COUNTIF(L2:L100,"?*"))

Thanks for your replt, Tomorrow i will try what you mean. What i want is really to have that range as source to one drop down list.

I will feedback you tomorrow after checking if this works.

Thanks in advance for your time and Patience

Originally Posted by Tony Valko

=OFFSET(L2,,,COUNTIF(L2:L100,"?*"))

Works perfectly, can't understand why is "?*" :/ although it works like this

Originally Posted by Tony Valko

The COUNTIF criteria "?*" means to count only those cells that contain printable ascii text characters.

A formula blank "" is not a printable ascii character so cells that contain those are not counted.

Wow, ok got it. Thanks