Me encontré un cliente que necesitaba tener siempre ordenados los elementos permitidos en una celda con validación de datos tipo lista... la solución que encontré fue emplear un método Range.Sort con un evento de hoja Worksheet_Change.
Veamos en qué consistiría el trabajo.
Tenemos un listado, en modo tabla (que llamaré 'TblCiudad'), que tiene algunas ciudades de España... a la que además hemos asignado un nombre definido:
ndCiudades =TblCiudad[ciudades]
En una celda D2 hemos configurado una validación de datos tipo Lista con origen en el nombre definido anterior 'ndCiudades'
El siguiente paso consistirá en añadir dentro de la ventana de código de la hoja de trabajo (Hoja1 para nuestro ejemplo) el siguiente evento Worksheet_Change:
Una vez insertado el evento podremos comprobar al cambiar algo en la tabla, y solo en la tabla, que se producirá la ordenación deseada... lo que inmediatamente repercutirá en el orden de la celda validada:
Veamos en qué consistiría el trabajo.
Tenemos un listado, en modo tabla (que llamaré 'TblCiudad'), que tiene algunas ciudades de España... a la que además hemos asignado un nombre definido:
ndCiudades =TblCiudad[ciudades]
En una celda D2 hemos configurado una validación de datos tipo Lista con origen en el nombre definido anterior 'ndCiudades'
El siguiente paso consistirá en añadir dentro de la ventana de código de la hoja de trabajo (Hoja1 para nuestro ejemplo) el siguiente evento Worksheet_Change:
Private Sub Worksheet_Change(ByVal Target As Range)
'evento que saltará ante cualquier cambio en la hoja..
Dim rngCiudades As Range
Set rngCiudades = Hoja1.Range("TblCiudad[Ciudades]")
'pero verificamos que únicamente ordenaremos cuando trabajemos en la tabla
If Not Intersect(Target, rngCiudades) Is Nothing Then
'conel méetodo Sort ordenamos el listado...
rngCiudades.Sort _
Key1:=rngCiudades, _
order1:=xlAscending, _
Orientation:=xlSortColumns, _
Header:=xlYes
End If
End SubUna vez insertado el evento podremos comprobar al cambiar algo en la tabla, y solo en la tabla, que se producirá la ordenación deseada... lo que inmediatamente repercutirá en el orden de la celda validada:
Ahora se podría hacer más fácil y rápido con las nuevas funciones matriciales dinámicas: =ORDENAR(A2:A15) te da esa solución... A ver si lo implantan ya para todos los usuarios!!!
ResponderEliminarCorrecto Sergio...
Eliminarpero de momento hay que esperar o pasarse a versiones 'Insiders'
;-)
Muchas gracias Ismael por este interesante artículo.
ResponderEliminarQuizás mi pregunta sea muy elemental, pero me he liado en el paso de asignar un nombre definido a la tabla de ciudades (tblCiudad). ¿Cómo configuras ndCiudades =TblCiudad[ciudades]?
Conozco la pestaña de nombres definidos, en fórmulas, pero no consigo configurarlo en la forma que comentas.
Saludos.
Carlos.
Hola Carlos
Eliminares más sencillo de lo que parece
Seleccionas el campo de la TblCiudad y con el rango seleccionados Asignas Nombre definido con ese nombre... bien desde el Cuadro de nombres o bien desde la ficha Fórmulas> grupo Nombres definidos>botón Asignar nombres
Slds
Hola Ismael, Excelente en ejemplo, yo al ver el ejemplo me pregunte: ¿Como hacer la validación de datos ordenada sin que la base de datos origen se ordene? y con la GRABADORA DE MACROS lo hice, claro con solo unos pequeños cambios en el código, esto para demostrar lo importante que es la GRABADORA DE MACROS, para los que no sabemos muy bien VBA-Excel.
ResponderEliminarSub Macro3()
Dim Uf&
Application.ScreenUpdating = False
Uf = Range("A" & Rows.Count).End(xlUp).Row - 1
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("G1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Hoja2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hoja2").Sort.SortFields.Add2 Key:=Range("G1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Hoja2").Sort
.SetRange Range("G1:G" & Uf)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("E2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$G$1:$G$" & Uf
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = True
End Sub
y en el código de la hoja:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCiudades As Range
Set rngCiudades = Hoja2.Range("TblCiudades[Ciudades]")
If Not Intersect(Target, rngCiudades) Is Nothing Then
Call Macro3
End If
End Sub
Lo hice en la hoja 2, los datos en la columna "A", Copio y ordeno los datos en la columna "G". y La validación de datos en E2.
Saludos.
;-)
Eliminargracias por el aporte y por compartir tu idea
Slds
Hola, disculpa mi molestia, mi duda es acerca de otro tema de EXCEL, me podrías ayudar, es acerca de una tabla de Posiciones de Equipos de fútbol:
ResponderEliminarColumna A: EQUIPO
Columna B: PARTIDOS JUGADOS
Columna C: PARTIDOS GANADOS
Columna D: PARTIDOS EMPATADOS
Columna E: PARTIDOS PERDIDOS
Columna F: GOLES A FAVOR
Columna G: GOLES EN CONTRA
Columna H: PUNTOS (HA CALCULAR, lo he realizado así =SUMA((C2*3)+(D2*1)+(E2*0)), EXISTE OTRA FORMA CON USO DE FUNCIONES?)
APARTE SE PIDE HACER UNA TABLA EN LA MISMA HOJA (PARTE DE ABAJO)
Columna A: EQUIPO
Columna B: PUNTOS (lo he realizado así con BUSCARV), Se puede hacer de otra manera?
Columna C: DIFERENCIA DE GOLES (HA CALCULAR, lo he realizado así =SI(F2>=0;F2-G2;0), EXISTE OTRA FORMA DE HACERLO CON FUNCIONES?)
Hola Sonia,
Eliminaren la columna H sería suficiente
=(C2*3)+(D2*1)
En la otra tabla, para las ordenaciones lee este post
tendrás una ordenación de los puntos de los equipos, y sobre ello podrás recuperar con funciones de búsqueda el nombre (col A).
Para la diferencia de goles simplemente =F2-G2
Saludos
Ok. Muchas gracias :)
Eliminar