Count IF and Conditional Formating
 

I am looking for a way to:

conditionally format a cell in row A by looking at the text in the top
of row A then search the top cells in a series of other columns for a
match in the top cell, and then go down in that row to the same row
down as in row A; if there is something in that cell then color format
the original cell.

Thanks Bob

Let's say the column headers to be searched are $K$1:$P$1, and a cell in
column A (say A7) is selected when you set up the formatting. Here's a formula
that will work:

=NOT(ISBLANK(OFFSET($K$1,ROW()-1,MATCH(A$1,$K$1:$P$1,0)-1)))

or, if the data is numeric and 0 isn't a legal value:

=OFFSET($K$1,ROW()-1,MATCH(A$1,$K$1:$P$1,0)-1)<0



On 3 Mar 2005 15:06:17 -0800, wrote:

I am looking for a way to:

conditionally format a cell in row A by looking at the text in the top
of row A then search the top cells in a series of other columns for a
match in the top cell, and then go down in that row to the same row
down as in row A; if there is something in that cell then color format
the original cell.

Thanks Bob

Here's a simpler formula which assumes that the active cell is in column A.
The "trick" is to just make the number 1000 big enough to include the maximum
possible number of rows. IOW, if you have data going down to row 2872, the
$1000 would have tobe $2872 or greater.

=NOT(ISBLANK(HLOOKUP(A$1,$K$1:$P$1000,ROW(),0)))

On Thu, 03 Mar 2005 18:05:31 -0600, Myrna Larson
wrote:

Let's say the column headers to be searched are $K$1:$P$1, and a cell in
column A (say A7) is selected when you set up the formatting. Here's a
formula
that will work:

=NOT(ISBLANK(OFFSET($K$1,ROW()-1,MATCH(A$1,$K$1:$P$1,0)-1)))

or, if the data is numeric and 0 isn't a legal value:

=OFFSET($K$1,ROW()-1,MATCH(A$1,$K$1:$P$1,0)-1)<0



On 3 Mar 2005 15:06:17 -0800, wrote:

I am looking for a way to:

conditionally format a cell in row A by looking at the text in the top
of row A then search the top cells in a series of other columns for a
match in the top cell, and then go down in that row to the same row
down as in row A; if there is something in that cell then color format
the original cell.

Thanks Bob