☰

¿Cómo creo una lista desplegable para una columna de valores que cambian constantemente en Excel 2010?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
¿Cómo creo una lista desplegable para una columna de valores que cambian constantemente en Excel 2010?

Tengo una hoja de cálculo de excel con una columna de años, con un encabezado:

Years
1993
1993
1994
1994
1994
...
2011
2011

Hay valores duplicados para los años y se agregarán filas adicionales con el tiempo.

Tengo otra celda que necesita mostrar una lista desplegable de años, pero solo los años únicos. Intenté usar la función de validación de datos en Excel 2011, pero tiene 2 problemas:

  1. Muestra los años duplicados.
  2. Le digo que use la columna completa e incluye las celdas vacías en la lista desplegable.

¿Cómo obtengo una lista desplegable de años que mostrará solo valores únicos y se actualizará automáticamente a medida que se agreguen filas adicionales?

Editar: un poco más de información. La lista desplegable se utiliza en una hoja separada para mostrar datos calculados, como un formulario de Access. El usuario puede elegir un rango de años y los datos se actualizarán en consecuencia. La hoja original es solo una lista de todos los datos.

Respuesta1

Para este tipo de validaciones, uso VBA + un truco sucio:

Primero, ingrese al editor VBA con Alt+F11. Luego, puse mi "Código de validación de lista dinámica" (tm) :) en la hoja de trabajo respectiva.

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

Este código actualiza la lista de validación de celdas con la lista generada por la fórmula ingresada en Datos->Validación->Mensaje de error->Título. De esta forma, cada celda con validación de lista puede tener su propia fórmula.

Luego, agrego un módulo (Insertar->Módulo) y luego coloco este código en el nuevo módulo:

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

Esta función devuelve todas las celdas del rango sin espacios en blanco ni repeticiones. Luego, en cada celda con validación de lista, agrego GenDynList(range) en el título del mensaje de error de validación de datos.

Respuesta2

Desordenado. No existe una forma integrada de hacerlo que se actualice automáticamente. Sería más sencillo crear una lista separada con todos los años posibles que podrían interesarle en lugar de intentar limitarla a aquellos en su conjunto de datos.

Respuesta3

Agregue una tabla dinámica a partir de los datos de otra hoja. Utilice Años como filas; el resto de la tabla es irrelevante. Ordene y filtre las filas según sea necesario (por ejemplo, filtre explícitamente OUT "[en blanco]"). Convierta las celdas donde están las etiquetas de las filas en el objetivo de su validación de datos.

Utilice un rango con nombre en expansión para utilizar siempre todas las etiquetas nuevas: http://www.ozgrid.com/Excel/DynamicRanges.htm

Repita lo mismo para otras columnas, como país, estado, etc. Si basa todos los pivotes en el mismo rango de datos, todos se actualizarán juntos cuando actualice cualquiera de ellos. Su proceso ahora será: agregar nuevos datos, actualizar los pivotes, usar la validación de datos actualizados.

Ideas adicionales: el uso de una tabla para los datos de origen facilitará la actualización de las tablas dinámicas, ya que siempre utilizarán la tabla completa. Como alternativa, utilice un rango con nombre en expansión como fuente de datos para evitar problemas al agregar más filas.

Tenga en cuenta que puede utilizar un rango de una hoja de trabajo diferente como fuente para la validación de datos si lo hace definiendo un rango con nombre. No puede utilizar un rango normal que se refiera explícitamente a otra hoja.

información relacionada