• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Flag Data Added to Your File

N

nnavarro

Member
Excel Gurus:


I have this file that I share with 40 people. The data dump has about 15 columns. The 15 columns come from a standard report. The information is considered static but sometimes we do manual adjustments by adding additional rows to the data dump. All fields are populated but one which is system generated. Is there a way to format that? Let’s say highlight in yellow if not all fields are populated. That way it jumps at me without having to do it manually.


Thanks,


NN
 
Hi ,


You can use Conditional Formatting , and specify the following formula to change the cell color to yellow :


=COUNTBLANK(Data_Range)=0


Data_Range is your range of 15 columns.


You can also use the opposite of this formula :


=COUNTBLANK(Data_Range)<>0


to select a green colour for the same cell.


Narayan
 
Hi ,


Sorry about the mistake in my earlier post , but the two formulae should be switched :


=COUNTBLANK(Data_Range)=0


checks whether all the cells are populated ; hence the colour for this may be green.


=COUNTBLANK(Data_Range)<>0


checks whether even one cell is blank ; hence the colour for this should be yellow.


Narayan
 
Hi Narayan!


Thanks for looking into this. I did try the formula above but I must be doing something wrong because is not doing anything. It either colors everything or doesn't at all. See my workbook. Can you look into it?


Thanks


NN


https://dl.dropbox.com/u/90166573/Conditional%20Formating.xlsx
 
Hi Navarro ,


As I understood your requirement , it was this : you have data spread over 15 columns ; out of these 15 , 14 are filled in by the user , while 1 is system generated.


You wanted this 1 system generated cell to be highlighted yellow if the remaining 14 columns in that row are not populated fully ; as an additional sign , that system generated cell could turn green if all the remaining 14 cells were filled in with data.


Is this correct ?


If so , then the Conditional Formatting is to be applied to cells in only one column viz. the system generated ones , and not to all the 15 columns.


If we consider your sample file , let us assume column C contains the system generated data ; in cells C2 through C10000 ( say ) , apply the following formula :


=COUNTBLANK($B2:$I2)>0


and select yellow as the colour.


Over the same range , apply a second formula :


=COUNTBLANK($B2:$I2)=0


and select green as the colour.


See if this is what you wanted.


Narayan
 
Hi Narayan,


I finally figured out what you meant. The file is working beautifully thanks to you. I really appreciate your help.


NN
 
You must log in or register to reply here.
Back
Top