83 - El Administrador de Nombres y sus múltiples usos.

Es una de las herramientas más útiles a la hora de trabajar con validación de datos, listas desplegables, Tablas y Macros. Veremos a continuación sus principales usos.

CASO 1: cuando una misma lista (con nro. fijo de elementos) será utilizada en 1 o varias hojas con celdas con validación de datos. O en formularios, en algún control desplegable. El típico caso es el de los meses del año o días de semana.
Muchos usuarios colocan la lista en la primera hoja donde la van a utilizar. Y en las celdas con validación, en el campo Origen colocan ese rango. O a veces colocan allí la lista de elementos separados con punto y coma.

El problema surge cuando necesitamos utilizar esa lista de meses o días en otras hojas y tenemos que repetir el rango o recordar en qué hoja se encuentra ya creada.

SOLUCIÓN: seleccionar el rango (en este ejemplo desde Enero a Diciembre) y desde el menú Fórmulas, Administrador de nombres, asignar un Nombre (por ej: Meses), en el campo Ámbito' optar por 'Libro' y en el campo 'Se refiere a' introducir o seleccionar la hoja y el rango ocupado por esa lista.

Luego en cada celda con validación de datos ( o en los desplegables de algún Userform) donde se requiere mostrar la lista para seleccionar algún elemento, la opción será colocar ese nombre como se muestra en la imagen siguiente:

CASO 2: una lista dinámica que será utilizada en varias hojas con celdas con validación de datos (o controles desplegables en un Userform). El caso típico son las listas de Conceptos que pueden irse incrementando.  

SOLUCIÓN: Seleccionar una celda de la lista y desde el Administrador de nombres, Nuevo. Asignar un nombre, Ambito = Libro y en el campo 'Se refiere a' colocar la siguiente fórmula: 

=DESREF(Listas!$B$16;0;0;CONTARA(Listas!$B$16:$B$200;1);-1)

Argumentos del ejemplo: nombre de la hoja que contiene a la lista (Listas!), la celda del primer elemento ($B$16) y la celda hasta donde puede llegar el total de elementos ($B$200)

CASO 3: cuando una columna de una hoja se utilice para mostrar en desplegables. Aquí la lista también será dinámica, es decir que a medida que se agreguen datos a la hoja se irá incrementando el nro. de elementos que se mostrará en los desplegables. El caso típico son las hojas 'Base' como lista de Productos, Clientes u otras bases del sistema.

SOLUCIÓN: Si se trata de una hoja en formato de rango, seleccionar la columna y utilizar la función DESREF tal como vimos en el Caso 2, permitiendo así el agregado de elementos en esa columna indicando un máximo de filas (en exceso) que se considere que puede llegar a tener la base. 

=DESREF(Clientes!$B$4;0;0;CONTARA(Clientes!$B$4:$B$20000;1);-1)

Si se trata de una hoja en formato Tabla, en el campo 'Se refiere a' colocar solamente el nombre de la Tabla y la columna. Aquí no es necesario utilizar la función DESREF.

=nbre_tabla[nbre_columna]

CASO 4: Uso de listas dependientes. Es decir, cuando según la selección en una lista será la lista que se mostrará en otro desplegable. Y así tantas listas como desplegables se encuentre en la hoja o en un formulario.

Algunos usos: 

1. Categoría de conceptos, Subcategoría.
2. Lista de países, provincias, departamentos, localidades.
3. Listado de carpetas, subcarpetas, archivos (se puede considerar varios niveles de subcarpetas)
4. Lista de Personal: Planta o Sucursal, Área de trabajo, Nivel o categoría, Nombre.

SOLUCIONES: habrá que crear listas y a cada una de ellas asignarles un nombre y un rango de valores. Ese rango puede ser fijo o dinámico (en este caso con el uso de la función DESREF.). Es necesario que cada lista tenga por nombre el elemento de donde proviene.

Para el pto.1: En la siguiente imagen vemos que tenemos varias listas de Conceptos. Al momento de registrar un pago vamos a elegir una categoría y según esa selección se nos mostrará la lista de subcategorías.

NOTA: Como en este caso el nombre creado como 'Conceptos' corresponde a celdas de una fila y no de columna, no es posible asignarlo en la propiedad RowSource del primer control ComboBox6. Sino que se tendrá que rellenar mediante programación al momento de Inicializar el formulario. 

Private Sub UserForm_Initialize()

For Each ct In Range("Conceptos")      'nombre del rango entre comillas

    ComboBox6.AddItem ct.Text

Next ct

'el resto de las instrucciones para este evento

Y para el segundo control desplegable, de Subcategorías, estas serán las instrucciones, donde la propiedad RowSource toma como rango el texto del control anterior.

Private Sub ComboBox6_AfterUpdate()

If ComboBox6 = "" Then

       ComboBox7.RowSource = "": ComboBox7.Clear    

Else

       ComboBox7.RowSource = "=" & Trim(ComboBox6.Text)

End If

End Sub

Para el pto.2:  Para la siguiente imagen se crearon las siguientes listas: 

• PAISES
• Argentina, Bolivia y el resto de países. 
• Cordoba, Misiones y el resto de provincias por cada país.
• Punilla, Calamuchita y el resto de departamentos por cada provincia.
• Cosquín, La Falda y el resto de localidades por cada departamento.

NOTA: Como el rango 'Paises' se encuentra en una columna (y no en fila como en el ejemplo anterior), aquí sí es posible establecer, desde el modo diseño, la propiedad RowSource del primer desplegable (PAIS)

Y para el resto de los controles seguimos el ejemplo del punto anterior, con los eventos Change o AfterUpdate. O como en el libro que se puede descargar (ver enlace al pie) donde utilicé el evento Enter.

IMPORTANTE: si se van a utilizar varias listas en el libro, recomiendo utilizar una hoja para contenerlas y así encontrarlas a todas en un mismo lugar.

Descargar libro de ejemplo desde aquí.

Ver video Nº 83 desde aquí.