excel - una - Formatee el color de la celda en función del valor en otra hoja y celda

formato condicional de una celda dependiendo del valor de otra (5)

Tengo un libro de trabajo con dos hojas. Me gustaría formatear el color de fondo de la celda en la primera columna de la hoja 1 según los valores de la segunda columna de la hoja 2.

Por ejemplo, si el valor de of sheet2, row 6, column 2 es decir, 4, entonces me gustaría que el color de fondo de la hoja 1, fila 4, columna 1 sea verde. Si ninguno de los valores en la hoja 2, columna 2 hace referencia a una fila particular en la hoja 1, me gustaría dejarlo sin color. No hay prohibición contra el mismo valor que aparece varias veces en la segunda columna de la hoja 2. Felicitaciones de bonificación si puede decirme cómo desarmar el color si se elimina el último valor en la hoja 2 que apunta a una fila en la hoja 1.

Estoy seguro de que para los asistentes de Excel, esto podría ser trivial, pero rara vez tengo ocasión de utilizar Excel y, desde luego, no tengo tiempo para convertirme en cinturón negro. ¿Alguien puede ofrecerme consejos, sugerencias o una fórmula rápida para hacer esto? Si esto va a tomar algún código complejo de VB para implementar, no vale la pena.

¡Gracias!

Así es como lo hice en Excel 2003 usando el formato condicional.

Para aplicar formato condicional a Sheet1 usando valores de Sheet2 , necesita duplicar los valores en Sheet1 .

Crear un espejo de Sheet2, columna B en la Hoja 1

Ve a la Sheet1 .
Inserte una nueva columna haciendo clic con el botón derecho en el encabezado de la columna A y seleccionando "Insertar".
Ingrese la siguiente fórmula en A1:
=IF(ISBLANK(Sheet2!B1),"",Sheet2!B1)
Copie A1 haciendo clic derecho y seleccionando " Copy ".
Pegue la fórmula en la columna A haciendo clic derecho en su encabezado y seleccionando " Paste ".

Sheet1 , la columna A ahora debería reflejar exactamente los valores en la Sheet2 , columna B

(Nota: si no te gusta en la columna A , funciona igual de bien en la columna Z o en cualquier otro lugar).

Aplicando el formato condicional

Permanece en Sheet1 .
Seleccione la columna B haciendo clic con el botón izquierdo en su encabezado.
Seleccione la opción de menú Format > Conditional Formatting...
Cambie la Condition 1 a " Formula is " e ingrese esta fórmula:
=MATCH(B1,$A:$A,0)
Haga clic en el botón Format... y seleccione un fondo verde.

Ahora debería ver el fondo verde aplicado a las celdas coincidentes en Sheet1 .

Ocultar la columna del espejo

Permanece en Sheet1 .
Haga clic con el botón derecho en el encabezado en la columna A y seleccione " Hide ".

Esto debería actualizar automáticamente Sheet1 cada Sheet2 se cambie algo en Sheet2 .

Estoy usando Excel 2003 -

El problema con el uso de formato condicional aquí es que no puede hacer referencia a otra hoja de cálculo o libro de trabajo en sus condiciones. Lo que puede hacer es establecer una columna en la hoja 1 igual a la columna apropiada en la hoja 2 (en su ejemplo = Hoja2! B6). Utilicé la Columna F en mi ejemplo a continuación. Luego puede usar el formato condicional. Seleccione la celda en la Hoja 1, fila, columna 1 y luego vaya al menú de formato condicional. Elija "Fórmula es" en el menú desplegable y establezca la condición en "= $ F $ 6 = 4". Haga clic en el botón de formato y luego elija la pestaña Patrones. Elige el color que quieras y listo.

Puede utilizar la herramienta de pintor de formato para aplicar formato condicional a otras celdas, pero tenga en cuenta que, de forma predeterminada, Excel utiliza referencias absolutas en las condiciones. Si desea que sean relativos, deberá eliminar los signos de dólar de la condición.

Puede tener hasta 3 condiciones aplicadas a una celda (use el botón agregar >> en la parte inferior del cuadro de diálogo Formato condicional), de modo que si la última fila es fija (por ejemplo, sabe que siempre será la fila 10), puede Úselo como una condición para establecer el color de fondo en ninguno. Asumiendo que el último valor que le importa está en la fila 10 (aún suponiendo que ha establecido la columna F en la hoja 1 en las celdas correspondientes en la hoja 2), establezca la 1ª condición en Fórmula Is = $ F $ 10 = "" y la patrón a Ninguno. Conviértalo en la primera condición y anulará las siguientes declaraciones conflictivas.

He hecho esto antes con el formato condicional . Es una excelente manera de inspeccionar visualmente las celdas en un libro de trabajo y detectar los valores atípicos en sus datos.

También puede hacer esto con rangos con nombre para que no tenga que copiar las celdas de Sheet1 a Sheet2:

Defina un rango con nombre, digamos Sheet1Vals para la columna que tiene los valores en los que desea basar su condición. Puede definir un nuevo rango con nombre mediante el elemento de menú Insert/Name/Define... Escriba su nombre, luego use el buscador de la celda en el cuadro Refers to para seleccionar las celdas que desea en el rango. Si el rango cambiará con el tiempo (agregue o elimine filas) puede usar esta fórmula en lugar de seleccionar las celdas explícitamente:
=OFFSET(''SheetName''!$COL$ROW,0,0,COUNTA(''SheetName''!$COL:$COL)) .
Agregue un -1 antes del último ) si la columna tiene una fila de encabezado.
Defina un rango con nombre, digamos Sheet2Vals para la columna que tiene los valores que desea formatear condicionalmente.
Use el diálogo de Formato condicional para crear sus condiciones. Especifique la Formula Is en el menú desplegable, luego ponga esto para la fórmula:
=INDEX(Sheet1Vals, MATCH([FirstCellInRange],Sheet2Vals))=[Condition]

donde [FirstCellInRange] es la dirección de la celda que desea formatear y [Condition] es el valor que marca.

Por ejemplo, si mis condiciones en Sheet1 tienen los valores de 1 , 2 y 3 y la columna que estoy formateando es la columna B en Sheet2, entonces mis formatos condicionales serían algo así como:

=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=1 =INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=2 =INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=3

A continuación, puede usar el pintor de formato para copiar estos formatos en el resto de las celdas.

Aquí está mi propia solución para restaurar los colores originales al copiar ciertas hojas o plantillas muy formateadas a una nueva hoja de cálculo. Copia todos los datos directamente, por lo que solo funciona si necesita copiar una hoja, no solo aplica colores a una hoja diferente. con diferentes datos:

copie el libro de trabajo de formato original ctrl + g y seleccione el rango apropiado

pégalo en la nueva hoja de trabajo, todos los colores serán cambiados

con el destino aún resaltado, haga clic derecho y vaya a "Pegado especial" y seleccione "Todo usando tema fuente", luego repita el pegado especial, solo con "Valores" esta vez, y debe ser idéntico a la hoja original que copió