Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
I appologize if this question has been asked, I could not find anything close
enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
Select the row in question.
Go to format--conditional formatting and enter the following formula: =ISBLANK(A1) and select the color you would like to appear. -- Brevity is the soul of wit. "LisaVH" wrote: I appologize if this question has been asked, I could not find anything close enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
Won't that only highlight the row if A1 is blank? I want the row to be
highlighted if any cell within that row contains a blank. "Dave F" wrote: Select the row in question. Go to format--conditional formatting and enter the following formula: =ISBLANK(A1) and select the color you would like to appear. -- Brevity is the soul of wit. "LisaVH" wrote: I appologize if this question has been asked, I could not find anything close enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
Applying that conditional formatting will highlight any cell in the row that
is blank. Counterintuitive, I know, but that's how conditional formatting with formulas works.... Dave -- Brevity is the soul of wit. "LisaVH" wrote: Won't that only highlight the row if A1 is blank? I want the row to be highlighted if any cell within that row contains a blank. "Dave F" wrote: Select the row in question. Go to format--conditional formatting and enter the following formula: =ISBLANK(A1) and select the color you would like to appear. -- Brevity is the soul of wit. "LisaVH" wrote: I appologize if this question has been asked, I could not find anything close enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
Hi LisaVH,
your column A,C,E... contains hard data one column per day your column B,D,F...contains your formulated total to date one column per day if there is a missing data on column A or C or E or on the Last formulated column, say F.. you want to make the whole row as yellow try in conditional format select one row with data (e.g. row(22)) formula is : =(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0) select color yellow as patterns. there are other shorter formula for this cond. format which can be solicited in this forum, from others. regards... -- ***** birds of the same feather flock together.. "LisaVH" wrote: I appologize if this question has been asked, I could not find anything close enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
Conditional Formatting
Formula Is: =COUNT($A$1:$Z$1)<COLUMNS($A$1:$Z$1) Adjust to suit "LisaVH" wrote: I appologize if this question has been asked, I could not find anything close enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
This works beautifully. Thank you very much!
LisaVH "driller" wrote: Hi LisaVH, your column A,C,E... contains hard data one column per day your column B,D,F...contains your formulated total to date one column per day if there is a missing data on column A or C or E or on the Last formulated column, say F.. you want to make the whole row as yellow try in conditional format select one row with data (e.g. row(22)) formula is : =(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0) select color yellow as patterns. there are other shorter formula for this cond. format which can be solicited in this forum, from others. regards... -- ***** birds of the same feather flock together.. "LisaVH" wrote: I appologize if this question has been asked, I could not find anything close enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
I have one more issue. Didn't think of this before, but zero is an
acceptable value in my data. As it stands, if the input data is zero, the row is highlighted. Is there a sneaky way around this?? LisaVH "LisaVH" wrote: This works beautifully. Thank you very much! LisaVH "driller" wrote: Hi LisaVH, your column A,C,E... contains hard data one column per day your column B,D,F...contains your formulated total to date one column per day if there is a missing data on column A or C or E or on the Last formulated column, say F.. you want to make the whole row as yellow try in conditional format select one row with data (e.g. row(22)) formula is : =(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0) select color yellow as patterns. there are other shorter formula for this cond. format which can be solicited in this forum, from others. regards... -- ***** birds of the same feather flock together.. "LisaVH" wrote: I appologize if this question has been asked, I could not find anything close enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format based on entire row
you're welcome Lisa,
maybe this mixed with OR equal to zero =OR(COUNTIF(22:22,"=0"),(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0)) -- ***** birds of the same feather flock together.. "LisaVH" wrote: I have one more issue. Didn't think of this before, but zero is an acceptable value in my data. As it stands, if the input data is zero, the row is highlighted. Is there a sneaky way around this?? LisaVH "LisaVH" wrote: This works beautifully. Thank you very much! LisaVH "driller" wrote: Hi LisaVH, your column A,C,E... contains hard data one column per day your column B,D,F...contains your formulated total to date one column per day if there is a missing data on column A or C or E or on the Last formulated column, say F.. you want to make the whole row as yellow try in conditional format select one row with data (e.g. row(22)) formula is : =(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0) select color yellow as patterns. there are other shorter formula for this cond. format which can be solicited in this forum, from others. regards... -- ***** birds of the same feather flock together.. "LisaVH" wrote: I appologize if this question has been asked, I could not find anything close enough in the archives. I am not sure that this is possible,but here goes....I have a spreadsheet that keeps a running daily total for a set of locations. Each day I add another two columns. "A" is todays data "B" is total to date, then "C" will be the next day's data and "D" will be that day's total to date. What I want is to have the entire row turn yellow is there is a blank cell anywhere in that particular row. This will tell me if there is data missing and that on day 2 the total to date is not correct. One problem I keep thinking that if I do set this up, how do I tell it not to look that the empty rows after my last day? Do I need to set up a range? Will I have to keep changing the format as I add new days? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format won't copy | Excel Discussion (Misc queries) | |||
Conditional Format a column based on another | Excel Discussion (Misc queries) | |||
Conditional Format based on Other Cells | Excel Discussion (Misc queries) | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
Conditional Format based on contents of cell. | Excel Worksheet Functions |