heretolearnexcel
Board Regular
- Joined
- Jan 22, 2019
- Messages
- 61
- Office Version
- 365
- Platform
- Windows
Hi,
I have the following data below.
I would like to use a conditional formatting formula to highlight each row when for instance "DILA" in the section "Existencia" is =0, and in "¿Está activa la promoción? 1= sí, 0= no" is =1. And also highlight with a different color when for Dila in the section "Existencia" is >0 and in "¿Está activa la promoción? 1= sí, 0= no" is =0
I came up with the following temprary solution using and AND formulas in the columns: L:P and Q:U, for when the conditions are met. I know the conditional formatting would apply the formatting when ANY of the columns (DILA, Matriz, etc) satisfy the condition, and that my temporary solution gives more detail, but I would still like to highlight the rows when one of the columns satisfies the condition.
Does anyone have any ideas on how to do this?
Thanks in advance, any help is greatly appreciated.
I have the following data below.
I would like to use a conditional formatting formula to highlight each row when for instance "DILA" in the section "Existencia" is =0, and in "¿Está activa la promoción? 1= sí, 0= no" is =1. And also highlight with a different color when for Dila in the section "Existencia" is >0 and in "¿Está activa la promoción? 1= sí, 0= no" is =0
I came up with the following temprary solution using and AND formulas in the columns: L:P and Q:U, for when the conditions are met. I know the conditional formatting would apply the formatting when ANY of the columns (DILA, Matriz, etc) satisfy the condition, and that my temporary solution gives more detail, but I would still like to highlight the rows when one of the columns satisfies the condition.
Does anyone have any ideas on how to do this?
Thanks in advance, any help is greatly appreciated.
promociones tnj.xlsx | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
1 | Existencia | ¿Está activa la promoción? 1= sí, 0= no | ¿Pendiente activar promoción? | ¿Pendiente inactivar promoción? | |||||||||||||||||||
2 | Código | Almacén General | Matriz | PLAZA PROGRESO | DILA | PLAZA LUCERO | Almacén General | Matriz | PLAZA PROGRESO | DILA | PLAZA LUCERO | Almacén General | Matriz | PLAZA PROGRESO | DILA | PLAZA LUCERO | Almacén General | Matriz | PLAZA PROGRESO | DILA | PLAZA LUCERO | ||
3 | 7500464581303 | 2 | 17 | 4 | 4 | 6 | 1 | 1 | 1 | 1 | 1 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | ||
resumen |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3 | B3 | =XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!C:C,"not found",0) |
C3 | C3 | =XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!E:E,"not found",0) |
D3 | D3 | =XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!G:G,"not found",0) |
E3 | E3 | =XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!I:I,"not found",0) |
F3 | F3 | =XLOOKUP($A3,'stock tnj'!$A:$A,'stock tnj'!O:O,"not found",0) |
G3:K3 | G3 | =SUMIFS(promociones!$O:$O,promociones!$B:$B,resumen!$A3,promociones!$A:$A,resumen!G$2) |
L3:P3 | L3 | =AND(B3>0,G3=0) |
Q3:U3 | Q3 | =AND(G3=1,B3=0) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
promociones!_FilterDatabase | =promociones!$A$1:$M$363 | G3:K3 |