Lista

hola

Tengo una lista en Excel (Datos --> Validación --> Permitir: lista).
El problema es que la cantidad de valores de referencia es variable. p.e.,
puede ser de A2 a A10
Lo que quiero es que la lista no me presente los blancos.

si los datos de tu lista estan en la en la columna A apartir de la fila 2
prueba lo siguiente

en la definicion de la lista prueba esta formula

ÞSREF($A$2;;;CONTAR($A$2:$A$65536);1)

solo ten en cuenta que si dejas espacios en blanco en la lista de datos no
dara buen resultado

un saludo
juan

Hola chicos,

"macussa" wrote in message

...una lista en Excel (Datos --> Validación --> Permitir: lista).
... valores ... tienen forma de texto
...la cantidad de valores de referencia es variable
...me salen todos los espacios en blanco que quedan abajo.

"Juan" wrote in message

ÞSREF($A$2;;;CONTAR($A$2:$A$65536);1)

o mejor aun para evitar la volatilidad de la funcion DESREF() y el problema de los espacios intermedios (aunque en todo caso la
lista quedaria fea con espacios intercalados), asi:

=$A$2:INDICE($A:$A;COINCIDIR(REPETIR("z";255);$A:$A))

Tambien podria ser buena idea asignarle un nombre a la formula y usarlo en otras formulas:
- menu Insertar>Nombre>Definir...
- nombre: Lista
- formula: =$A$2:INDICE($A:$A;COINCIDIR(REPETIR("z";255);$A:$A))
- Aceptar
- menu Datos>Validacion...
- Permitir: lista
- Formula: =lista

Saludos,
KL

hola kl

gracias por la mejora.

solo una cuestion, en la formula que yo he propuesto si la lista es solo de
texto dara error por el empleo de CONTAR, que se corrige con CONTARA en la
que tu has propuesto si solo hay numeros da un error, en el caso de tener
letras intermedias y algun numero el listado sera incompleto

un saludo
juan

Hola Juan,

que tu has propuesto si solo hay numeros da un error, en el caso de tener letras intermedias y algun numero el listado sera
incompleto

1) Solo expuse la formula para listas dinamicas de texto ya que macussa mencion expresamente que los valores son texto.

2) Si necesitas manejar solo numeros transforma la formula de la siguiente manera:

=$A$2:INDICE($A:$A;COINCIDIR(1e307;$A:$A))
o mas exactamente:
=$A$2:INDICE($A:$A;COINCIDIR(9,99999999999999E307;$A:$A))

3) Y si tienes numeros y texto mezclados puedes hacer algo asi definiendo 3 nombres (o simplemente usando tres formulas en la hoja):

TXT =SI(CONTAR.SI($A:$A;"*");COINCIDIR(REPETIR("z";255);$A:$A))
NUM =SI(CONTAR($A:$A);COINCIDIR(1e307;$A:$A))
LISTA =$A$2:INDICE($A:$A;MAX(TXT;NUM))

Tambien se podria meterlo todo en una formula:

=$A$2:INDICE($A:$A;MAX(SI(CONTAR.SI($A:$A;"*");COINCIDIR(REPETIR("z";255);$A:$A));SI(CONTAR($A:$A);COINCIDIR(1e307;$A:$A))))

Nota:
-
Si se quiere asignar esta formula larga a un nombre definido, se corre el riesgo de que no funcione debido a que se anaden
automaticamente los nombres de hoja a todas las referencias de rangos y se puede sobrepasar el limite de 255 caracteres en un nombre
definido facilmente. Por eso la solucion de partir la formula en 3. P.ej:

='Informe Anual de Facturacion'!$A$2:INDICE('Informe Anual de Facturacion'!$A:$A;MAX(SI(CONTAR.SI('Informe Anual de
Facturacion'!$A:$A;"*");COINCIDIR(REPETIR("z";255);'Informe Anual de Facturacion'!$A:$A));SI(CONTAR('Informe Anual de
Facturacion'!$A:$A);COINCIDIR(1e307;'Informe Anual de Facturacion'!$A:$A))))

Al parecer si la hoja ya ya tiene un nombre lo suficientemente largo como para sobrepasar el limite mencionado, no se puede
introducir la formula completa en la casilla 'se refiere a...' de un nombre definido (se cortaria sola en el caracter numero 256),
pero parece que se le puede enganar a Excel haciendo una pequena trampa:

- cambiar el nombre de la hoja a uno relativamente corto (suficiente para introducir la formula completa), digamos "Hoja1".
- luego creamos el nombre definido(Insertar>Nombre>Definir...) y le asignamos la formula que seguidamente se convertira en algo asi:
=Hoja1!$A$2:INDICE(Hoja1!$A:$A;MAX(SI(CONTAR.SI(Hoja1!$A:$A;"*");COINCIDIR(REPETIR("z";255);Hoja1!$A:$A));SI(CONTAR(Hoja1!$A:$A);COINCIDIR(1E+307;Hoja1!$A:$A))))
- una vez cerrado el dialogo de nombres definidos procedemos a cambiar el nombre de la hoja al que nos guste
- comprobamos que la lista dinamica sigue funcionando asignando el nombre a una lista de validacion o simplemente introduciendo en
una celda =Lista, seleccionando la formula y pulsando F9.
- comprobamos en el dialogo de nombres definidos la formula esta reflejada incorrectamente, pero funciona :-)
Esta solucion me funciona en XL2000 y XL2003

Una otra posible solucion seria la de hacer las referncias a hojas relativas anadiendo un signo de exclamacion delante de cada
rango, pero entonces la lista variara segun en que hoja se use el nombre (y el uso del nombre como tal perderia la mayor parte de su
sentido):

=!$A$2:INDICE(!$A:$A;MAX(SI(CONTAR.SI(!$A:$A;"*");COINCIDIR(REPETIR("z";255);!$A:$A));SI(CONTAR(!$A:$A);COINCIDIR(1E+307;!$A:$A))))
-

Y otra posibilidad aunque bastante mas lenta (solo para rangos reducidos:

=$A$2:INDICE($A:$A;COINCIDIR(2;1/($A$1:$A$25<>"");$A:$A))
donde $A$1:$A$25 es el rango maximo en el que puede estar la lista

Saludos,
KL

hola kl

de nuevo muchas gracias por tu completa respuesta y por tu tiempo

un saludo
juan