Skip to main content

worksheet function - Excel conditional formatting based on a formula


I would like to use Excel's conditinal formatting of a particular column based on values in two other columns.



  • I have values in columns A, B, C and D.

  • column A has text values (any value user enters)

  • column B has a numeric value (any value user enters)

  • column C has a text value selected from a set of values (data validation using drop-down):

    • Start

    • Intermediate

    • End




  • column D is defined as (x stands for any row number)


    =IF(Cx="End";"";Bx)


  • user can as well delete the formula in column D thus making it empty




I want values in column A displayed in red when D is empty and user didn't select End in column C. So this only happens when a user deletes the formula in D.


So I thought of creating a conditional formatting rule that would do the trick for me. But when I try to write the condition formula it doesn't work.


I've tried specifying this formula for conditional formatting rule:


=NOT(OR(ISNUMBER(INDIRECT("D"&ROW()));INDIRECT("C"&ROW())="End"))

but it didn't work.


Are there any limitations which functions can be used in this formula, because when I use something like OR() or AND() it seems it simply stops working even though they're simple boolean functions that return TRUE/FALSE...



Answer



Based on this Superuser question I was able to solve my problem.


Here's the solution that actually works. Replace the formula with this one:


=NOT(OR(ISNUMBER($D1);$C1="End"))

And it will work. Row number will get recalculated for each row, but columns are statically set so they'll refer to correct ones.


Even simpler


But if we consider that ISBLANK() function returns FALSE where there's a formula in a cell as well, we can simplify this even further by using this formula only:


=ISBLANK($D1)

Comments

Post a Comment

Popular Posts

keyboard - Is there any utility/method to change Windows key bindings to type rare chars to currently empty bindings?

I'm currently typing this post with my windows XP machine and (Spanish) keyboard, and I'd like to add some extra symbols to my text. I could open the "char map" windows utility, look for the desired symbols, and paste them. But I'd like something quickier. For example, when I'm using my OSX Mac at work, I can easily add a ©, ™, ® or similar symbols, just pressing some weird ALT-GR + G / H / J, key combinations. In my (Spanish) keyboard mapping, these combinations are empty, as they don't produce any char at all, which, on the other hand, is perfectly normal and desirable. So, I thought: Why couldn't I add some extra key mappings on top of my currently empty ALT-GR + G/J/H Keys in my Spanish keyboard, and thus, being able to quickly type these special symbols? So that's my question: Is there any utility/method to achieve that effect under windows? (My version is XP). I've even googled this for some time but no luck. I've been a long term Hot...
Post a Comment
Read more

virtualization - How to select paravirtualization interface in VirtualBox?

Given a windows 8 host system (Intel Core i5) and a Linux Fedora host, I would like to determine the optimal setting for the paravirtual interface. Options are none Default Legacy minimal Hyper-V KVM This page suggest the selection is only based on the guest system: The biggest change in VirtualBox 5.0 is the introduction of paravirtualization support, bringing higher performance and time-keeping accuracy to supported guest operating systems (Hyper-V on Windows and KVM on Linux). Is that correct? Answer The VirtualBox Manual , in the section titled Paravirtualization providers explains very clearly when each should be used (emphasis added): Minimal: Announces the presence of a virtualized environment. Additionally, reports the TSC and APIC frequency to the guest operating system. This provider is mandatory for running any Mac OS X guests. KVM: Presents a Linux KVM hypervisor interface which is recognized by Linux kernels starting with version 2.6.25. VirtualBox's implementati...
Post a Comment
Read more

macos - Visit URL in running Firefox instance from command line in OS X

I have Firefox 3.6 installed on my OS X (10.6) box. I would like to open an URL in the running Firefox instance (in a new tab or window, no matter) from the command line. I tried several ways, neither switches -new-tab , -new-window , -url , or none helped me. I always get the A copy of Firefox is already open. Only one copy of Firefox can be open at a time. error message. Answer running open http://www.superuser.com from the command line will (at least for me) open a new tab to superuser.com in firefox (which is my default browser)
Post a Comment
Read more

How can I migrate a Windows 10 from BIOS/MBR boot to UEFI/GPT without reinstalling?

I've installed Windows 10 in BIOS mode and use MBR. How can I convert it to use UEFI and GPT without reinstalling Windows 10? Is this possible? Answer In Windows 10 v1703 (Creators Update, Build 15063), there is a tool called MBR2GPT.EXE included which does all steps . Best is to boot from a Win10 v1703 DVD/USB drive to the repair option (WinPE) and run Command prompt (cmd.exe) under repair options . Now use diskpart and list command to see all drives. X:\>diskpart Microsoft DiskPart version 10.0.15063.0 Copyright (C) Microsoft Corporation. On computer: Win10v1703 DISKPART> list disk Disk ### Status Size Free Dyn Gpt -------- ------------- ------- ------- --- --- Disk 0 Online 60 GB 0 B Now look or the disk number where your Windows is installed. This number is important for next step. Now run mbr2gpt /convert /disk:ID with the ID: X:\>mbr2gpt /convert /disk:0 MBR2GPT will now attempt to convert disk 0. If conversion is s...
Post a Comment
Read more