Tengo una columna que usa valores del conjunto predefinido (una lista desplegable), implementada usandoValidaciones de datos. Ahorasi cambio algún valor en la lista, el menú desplegable (cuando esté abierto) ofrecerá inmediatamente este nuevo valor. Sin embargo,la tabla no se actualizará automáticamente, lo que significa que algunos de los valores de la columna no serán válidos hasta que los solucione manualmente.
He visto soluciones basadas en macros/VBA, pero ¿no existe alguna forma inteligente directamente en la interfaz de usuario de Excel?
Respuesta1
Como mencioné en los comentarios, la única forma de hacerlo sería con VBA.
Aquí hay una opción. Agregué comentarios a lo largo del código. Esto supone que está utilizando un rango con nombre para la lista de validación denominada "Lista" y que está en la misma hoja que las celdas que se están validando.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim isect As Range
Dim vOldValue As Variant, vNewValue As Variant
Set isect = Application.Intersect(Target, ThisWorkbook.Names("List").RefersToRange)
If Not isect Is Nothing Then
' Get previous value of this cell
Application.EnableEvents = False
With Target
vNewValue = .Value
Application.Undo
vOldValue = .Value
.Value = vNewValue
End With
' For every cell with validation
For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
With cell
' If it has list validation AND the validation formula matches AND the value is the old value
If .Validation.Type = 3 And .Validation.Formula1 = "=List" And .Value = vOldValue Then
' Change the cell value
cell.Value = vNewValue
End If
End With
Next cell
Application.EnableEvents = True
End If
End Sub
También puedes descargar elhoja de cálculo de ejemploMe reuní para probar esto. (¡Contiene macros!)
Respuesta2
¿Pero no existe alguna forma inteligente directamente en la interfaz de usuario de Excel?
Creo que conozco uno, al menos parece satisfacer todas tus peticiones:
- Necesitas configurarRegión nombrada que cambia dinámicamentecomo fuente para la Validación de Datos. Esto se puede lograr usando
OFFSET
la función. Suponiendo que tiene la lista de valores para el cuadro desplegable en la columna A deSheet1
(el nombre solo importa para la fórmula), la celdaA1
tiene un encabezado, por ejemploList of values
, y los valores se colocan al principioA2
y debajo, debe hacer lo siguiente: vaya a la cintaFormulas > Name Manager
, cree una nueva región ( llamémosloItems
) y establezca su región NO apuntando a las celdas, sino usando la siguiente fórmula:=OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1)
. - ColocarValidación de datosreglas: en lugar de apuntar al rango de celdas de la Lista, escriba
=Items
; de este modo, su región nombrada se utilizará como fuente de elementos de la lista. - Como resultado de lo anterior, recibirá una lista verdaderamente dinámica que cumple con todos sus requisitos: puede cambiar/agregar elementos libremente a la columna A, y estos cambios se reflejarán inmediatamente en el menú desplegable la próxima vez que la use. Al mismo tiempo, los viejos valores permanecerán intactos.
Utilizo esta solución durante aproximadamente 2 años en mi trabajo. ¡Espero que también te resulte útil!
PD: aquí está el archivo de muestra real:Menú desplegable dinámico