Lista con rango dinámico en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Podemos crear una lista con rango dinámico en Excel, con las referencias deseadas y que se actualicen mediante el rango dinámico.

La lista con validación de datos muestra los valores fijos, necesitando modificar la referencia del rango del origen de los datos. Si el rango incluye celdas vacías, el desplegable de la lista mostrará las celdas vacías.

Emplearemos las siguientes funciones, anidándolas en la validación de datos:

  • Función DESREF. Devuelve un desplazamiento de referencia a partir de una referencia determinada.

  • Función CONTARA. Cuenta cuántos valores hay en la lista de argumentos.

Lista con rango dinámico en Excel: Fórmula anidada =DESREF(F2;0;0;CONTARA(F:F);1)
  1. ORIGEN DE LOS DATOS. Ir a: Celda F2 / Escribir las delegaciones deseadas. Valores que se mostrarán en la lista, posteriormente se podrán añadir nuevos valores.

  2. Ir a: Celda C2. Celda donde aplicar validación de datos.

  3. VALIDACIÓN DE DATOS. Ir a: Cinta de opciones / Pestaña: Datos / Grupo: Herramientas de datos / Validación de datos / Validación de datos. Mostrará la ventana de insertar validación de datos.

    Lista con rango dinámico en Excel: Ventana Validación de datos con tipo lista y orígen como =DESREF(F2;0;0;CONTARA(F:F);1)
  4. Ir a Pestaña: Configuración / Permitir / Seleccionar: Lista. Tipo de validación deseada.

  5. Ir a: Origen / Escribir:

    =DESREF(F2;0;0;CONTARA(F:F);1)
  6. Ir a: Celda C2 / Seleccionar la delegación deseada. Mostrará los valores del rango F2:F6.

    Lista con rango dinámico en Excel: Celda C2 con lista actualizada con el valor de F7
  7. NUEVOS VALORES. Ir a: Celda F7 / Escribir: Málaga. O las delegaciones deseadas.

  8. Ir a: Celda C2 / Verificar como se muestra el nuevo valor.

    La columna F de origen de datos se puede ocultar o insertar en otra hoja y ocultar la hoja), evitando la manipulación accidental.

Así funciones y validación de datos podemos crear una lista con rango dinámico en Excel.

Nivel de dificultad: Avanzado Excel Avanzado

4 comentarios en “Lista con rango dinámico en Excel

    • Hola Roger,

      La fórmula que aparece en el tutorial está correcta.

      Te recomiendo que la revises, comprueba que trabajas con Excel en Español, en caso contrario deberás de cambiar el nombre de las funciones y los punto y coma por comas.

      Un saludo,

      Trucos y Cursos de Excel

  • Muy bueno, lo he utilizado dando como referencia un filtro de una tabla dinámica y también funciona, con lo que se actualiza automáticamente cuando cambia el valor del campo filtro.
    muy, muy bueno.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *