Windows 10: FORMATO CONDICIONAL EXCEL CON VARIAS CONDICIONES

Hola buen dia, quisiera su ayuda con lo siguiente porfavor:


Quiero que se de color a aquellas celdas de la COLUMNA "A" EN LA "TABLA 3", siguiendo los criterios establecidos en la "TABLA 1", los cuales a su vez estan relacionados con la "TABLA 2", en la cual se indican los valores de dichos intervalos.


Es importante mencionar que los datos de la TABLA 2 estan en una pestaña y los de la TABLA 3 se encuentran en otra diferente.


Descripcion del problema:


Realizo el procedimiento normal de seleccionar la columna A de la TABLA 3 (que es donde deseo aplicar los colores) y procedo a ir a la opcion de formato condicional para indicar la formula que quiero utilizar de la siguiente manera:

=Y(C3=colors!$B$4;B3<colors!$C$4;B3>colors!$E$4)_____________Esta es la formula que da color a los rangos para la categoria "elefantes"

=Y(C3=colors!$B$5;B3<colors!$C$5;B3>colors!$E$5)_____________Esta es la formula que da color a los rangos para la categoria "jirafas"


*colors! hace referencia al nombre de la pestaña en la que se encuentra la TABLA 2 y en la cual busco la celda que contiene el valor del rango. Es importante mencionar que dicho valor puede cambiar en cualquier momento y es por ello que enlazo la formula a la respectiva celda en lugar de escribir los numeros directamente sobre la formula.


Al ejecutarla no cumple los criterios.


TABLA 1. COLORES

COLOR ​

INTERVALO CORRESPONDIENTE​

AZUL ENTRE 1 Y 3
VERDE ENTRE 2 Y 4
AMARILLO DEBAJO DEL INTERVALO 3
MORADO POR ENCIMA DEL INTERVALO 4



TABLA 2. INTERVALOS (ubicada en la pestaña "colors!")


B C D E F
3
INTERVALO 1 INTERVALO 2 INTERVALO 3 INTERVALO 4
4 ELEFANTES 4,95 6,83 4,25 7,54
5 JIRAFAS 5,17 5,53 5,03 5,66



TABLA 3. DATOS PARA APLICAR COLORES (SE ENCUENTRAN ESCRITOS EN PESTAÑA DIFERENTE)

COLUMNAS/FILAS DE EXCEL
B C
2

CANTIDAD ​

ANIMAL
3 4,3 elefantes
4 5,1 jirafas
5 7 elefantes
6 5,6 jirafas
7 4 elefantes
8 5 jirafas
9 8 elefantes
10 6 jirafas
11 0 elefantes
12 0 jirafas

:)

 

Nokia 7110

Do you think its still posible to find a good working nokia 7110 battery? I have a phone thats in pretty good condicion and only needs a new battery, the rest parts are fine.

 

Export All Administrative Events to Excel


To analyze events, from the Windows Event Viewer, there is a simple way to export all Administrative Events to Excel, with PowerShell.

Exporting all Administrative Events to Excel is a simple two Step process, as described here:

Step 1 - Create the Administrative Events View .xml file

1. Open Eventviewer (%windir%\system32\eventvwr.msc)
2. Navigate to: Event Viewer (Local) > Custom Views > Administrative Events
3. In the “Actions” pane select “Filter Current Custom View”.
4. Select the the XML tab.
5. Press Ctrl+A to select all the XML code of the Custom View.
6. Open a notepad, paste the selected code and save the file to your Desktop as AdmEvtView.xml

Step 2 - Create the csv file with the events

1. Download the ExportEvtCSV.zip file, which contains the script ExportEvtCSV.ps1 and unzip it, on your Desktop.
   It's not a fancy script, just basic PowerShell commands to create a csv file on the Desktop.
2. In Windows Search, type “ISE” (without the quotes) to open “Windows PowerShell ISE” and Run as administrator
   
3. To allow running the script, change the ExecutionPolicy, for this session. To do that, in the Console pane type:
   Code:
   
   Code:

   Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass -force

4. In the Windows PowerShell ISE, open and run the script: ExportEvtCSV.ps1
   The script will create a csv file with a name YYYYMMDD.HHMM.csv on the Desktop
5. When done, open the newly created .csv file, format the columns as needed and optionally save it as .xlsx, if you wish.

That’s it! You now have all the Administrative Events in Excel for filtering and further analysis.

Now to the more technical hard stuff... *Confused

There is a reason for running the script from within PowerShell ISE!

It would be great if everything was also working perfectly, when running the script from an elevated PowerShell too.

We can run it from an elevated PowerShell, which means that you just follow the Step 1, as above but for the Step 2 instead of the ISE you run the script from an elevated PowerShell.

The problem is that it will work only for anybody who has en-US format for the dates. Everyone else, who has another format (i.e. en-GB, fr-FR, el-GR etc.), the dates are not translated properly by Excel (although the script uses the –UseCulture switch) and remain as text in the en-US format.

I'm not sure if this a bug of the "export-csv" cmdlet, but although it runs the way it supposed to from within the ISE, from PowerShell there is a problem with the dates format.
As I haven’t found a way to overcome this obstacle, any suggestion from the PowerShell gurus of the forum (like my good friend Shawn @Brink, for instance), is welcome.

 

CSV files used to open in Excel....not now!


Hi, you say Excel is installed. Does it run normally?

You can't find Excel in (an unspecified) list of applications. I don't know which list that is for sure.

For example, in the list related to:
- rt click a file. Open With
- this list:



- this list:




- some other list...

If Excel does not appear in the above, I suggest you repair your office installation.

Maybe the above gives you some ideas. Otherwise please post a screenshot of where you think you should see it but don't. Thanks.