☰

Excel 2013 - Validación de datos - Cree una lista desplegable con valores si se cumple un segundo criterio

tag-icon • tag-icon microsoft-excel vba
Excel 2013 - Validación de datos - Cree una lista desplegable con valores si se cumple un segundo criterio

Voy a simplificar mi problema tanto como sea posible. Obtuve dos hojas en Excel. En la primera hoja estoy creando una lista de ubicaciones. Necesito elegir un área donde se encuentra la ubicación en la columna A antes de poder escribir la ubicación en la columna B. En la siguiente columna (C) habrá una identificación que se crea automáticamente (para la primera ubicación ID-001, el segundo obtiene el ID 002, etc.).

En mi segunda hoja estoy creando una lista de activos. Por lo tanto, nuevamente, elijo un área en la columna A y escribo el nombre del activo en la columna B. Ahora quiero tener una lista desplegable en la columna C donde me muestre solo aquellos ID de la primera tabla donde está el área. lo mismo. Intenté usar la función OFFSET pero el punto que falta es algún tipo de "seleccionar si" (como countif o sumif) para limitar la lista de ID a aquellos que tienen la misma área.

Puede haber muchas ubicaciones y activos en cada área, pero cada ubicación y cada activo solo se puede asignar a una ubicación.

¡Espero sus respuestas chicos! Una solución de Excel o una solución de VBA sería fantástica.

Respuesta1

Sugiero crear una columna auxiliar que incluya valores únicos para usar en BUSCARV. Por ejemplo; digamos que tienes Área1, Área2, Área3 y Área1 (nuevamente) y continúa. Combine estos valores con valores incrementales (por ejemplo, 01, 02, 03, ...) y cree 01Area1, 01Area2, 01Area3 y02Área1. Ahora ya sabe cómo llamar a cada elemento de la lista en la segunda hoja. Si seleccionasÁrea1en la columna A, entonces necesitas buscarnúmero incremental&Área1. Los números incrementales por valores se pueden crear medianteCONTAR.SIfunción que utiliza rango en expansión:

=COUNTIF($A$2:A2,A2)

Al expandir el rango, la fórmula cuenta solo los valores hasta su propia fila. La única desventaja de este método es que necesita crear elementos de lista enen otra parte

Aquí dos enlaces que describen un uso similar:

  1. https://www.spreadsheetweb.com/how-to-get-nth-match-with-vlookup/
  2. https://www.spreadsheetweb.com/pricing-list-quoting-tool-part-1/

Respuesta2

Suponiendo que sea aceptable ordenar la tabla Ubicaciones, la solución más simple implica solo una fórmula de validación de datos relativamente corta y un par de rangos con nombre. No se requieren columnas ni filas auxiliares.

Configure dos hojas de trabajo de ejemplo Locationsy Assetsasí:

Captura de pantalla de la hoja de trabajo de Ubicaciones  Captura de pantalla de la hoja de trabajo de Activos

Agregue dos nombres definidos:

  1. Areas→=Locations!$A:$A
  2. IDs→=Locations!$C:$C

Finalmente, agregue una validación de datos de lista desplegable en la celda C2en la Assetshoja de trabajo con la siguiente fórmula y luego complete/copie y pegue la celda hacia abajo:

=INDEX(IDs,MATCH(A2,Areas,0)):INDEX(IDs,MATCH(A2,Areas,0)+COUNTIF(Areas,A2)-1)

Los inconvenientes de este método son:

  • Es necesario utilizar nombres definidos.
  • La tabla de Ubicaciones debe estar ordenada.
  • Otras tablas de la hoja Ubicaciones no pueden intersecar de forma segura la Areacolumna a menos que se garantice que no contienen valores que coincidan con los Areavalores de la tabla Ubicación.

La siguiente solución alternativa supera los inconvenientes excepto la de otras tablas. Sin embargo, sí utiliza celdas en la hoja de cálculo Activos para almacenar los elementos de las listas desplegables.

La hoja de trabajo de Activos está configurada con columnas adicionales:

Captura de pantalla de la hoja de trabajo de Activos

Esta vez, la C2fórmula de validación de datos es:

=IF(SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))=0,$I$1,C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2)))-SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))))

Ingrese esta fórmula D2y complete:

=
IF(
  SUMPRODUCT(
    --ISNA(
      E2:INDEX(
        (2:2),
        COLUMN(E2)-1+MAX(1,COUNTA(E2:INDEX(2:2,COLUMNS(2:2))))
      )
    )
  )=0,
  "ERROR",
  "ok"
)

Matriz de varias celdas: ingrese esta última fórmula en las celdas comenzando desde E2y hasta el número máximo de ubicaciones esperadas en un área (para el ejemplo que se muestra, he usado E2:I2):

=
INDEX(
  Locations!$C:$C,
  IFERROR(
    SMALL(
      IFERROR(1/(1/(
        (Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A))=IF($A2="",NA(),$A2))
        *ROW($A$2:INDEX($A:$A,COUNTA(Locations!$A:$A)))
      )),FALSE ),
      COLUMN(INDEX(2:2,1):INDEX(2:2,MAX(2,COUNTIF(Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A)),$A2))))
    ),
    NA()
  )
)

información relacionada