Wednesday, September 21, 2016

Conditional Formatting using AND/OR Formulas? [Anwsered]

Conditional Formatting using AND/OR Formulas?

Hi

I have some data in Excel and I want to apply various formatting if it meets
certain critiera.
Could anyone help me out with the formulas I need?

Where Country = "England", And Income is <£16,000 And Verification= Y and
Mode = F and Reason is LLL OR MMM conditional formatting should be Green.

Where Country = "England", And Income is <£16,000 And Verification= Y and
Mode = F and Reason is Null conditional formatting should be Yellow

Where Country is Not England OR Income is >£16000 OR Verification is not Y OR
Mode is not F AND Reason is Not Null conditional formatting should be Red

I take it there is no way to have more than 3 conditional formats?

Also this will be ever expanding data set so can I just replicate the
conditional formatting over blank rows and then when data is added the
formatting will be automatically applied?

Thank you in anticipation
Emma

Keys to the Problem Conditional Formatting using AND/OR Formulas?

Download SmartPCFixer to Fix It (Free)

If A2 contains England, B2 contains 15000, C2 contains Y, D2 contains F and E2 contains LLL or MMM then you can use:
=AND($A2="England",$B2<16000,$C2="Y",$D2="F",OR($E2="LLL",$E2="MMM"))
as the formula for the first of your conditions.
Note the use of $ in the above formula.  This means it will work on any row, just make sure that A2 is the Active cell when you apply the CF, i.e.
click on cell A2, then drag across and down to select your data, then A2 will be the Active cell in the selection
- all other cells' CF will be relative to row 2 which is the same row that we used in the CF formula so they will look at their own row (hope that makes sense).
Yes, I think you are limited to 3 conditions in Excel 2003.  Using Format Painter should replicate the CF on additional rows if required (but inserting a row before the last row would do this automatically, I think).
Hope that helps.
Cheers
Rich

Tweak IE Options
  • Open Internet Explorer.
  • Go to "Tools > Internet Options > Advanced Tab".
  • Uncheck "Enable third-party browser extensions".
  • Click "Apply".
  • Reboot your computer.

Another Safe way to Fix the Problem: Conditional Formatting using AND/OR Formulas?:

How to Fix Conditional Formatting using AND/OR Formulas? with SmartPCFixer?

1. You can Download Error Fixer here. Install it on your system. When you open it, it will perform a scan.

2. After the scan is done, you can see the errors and problems need to be fixed. Click Fix All.

3. When the Fixing part is finished, your computer has been speeded up and the errors have been removed


Related: Best Way to Download Satellite L645-SP4004M WinDVD BD for Toshiba v.10.0.6.110 driver,Download Toshiba Satellite C655D-S5057 Wireless LAN Driver v.8.0.0.258.0,Method to Update & Download Toshiba Satellite L650-BT2N22 Web Camera Application v.2.0.3.37 driver,How Can You Update & Download Toshiba Satellite L775D-S7220GR Value Added Package v.1.6.0130.640204 driver,Download Toshiba Satellite Pro L750-SP5176FM Sleep Utility v.1.4.0025.000101 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: connecting to store through WIFI,Tech Support: Computer freezes trying to open files or launch programs from desktop,Troubleshooting:Conditional Format cell with date between 15 & 44 days and more than 45 days from todays date,Fast Solution to Error: Computer Reboots Every Hour,Fast Solution to Error: Computer freezes during video or when openining browsers. Have to hot reboot every time.,Can't change homegroup sharing permission,Can't connect XP shared printer to Win7 except as a local printer.__,Can You Connect the LAN Adapter for Wii On your Windows 7 Laptop?,Can These Files Be Deleted,Can’t install Microsoft Flight Simulator X Gold Edition error 1722

No comments:

Post a Comment