I want to highlight entries in a table that occur in a seperate list.

My table looks something like this:


A B C D
E A F G
H D J K


And the "list" which is just a range of cells looks like this:

H
A
F


I'm using Excel 2007. I tried selecting both ranges that represent
the table and list and do conditional highlighting so that any
duplicate entries are highlighted except there is one problem with the
way that worked. Since my table has some duplicate entries itself it
highlighted "A" (as I intended, because it was in the table and list)
but highlighted "D" also (not as I intended, because it was in the
table twice).

Any hints as to how I might get what I want accomplished (Highlight
cells in the table that occur in the list)?

Assumed that your table below has the "A" value in cell A4; you can format
the A4 cell with this formula;

=ISERROR(VLOOKUP(A4;CheckList;1;FALSE))
The matched items will get the formatting

=NOT(ISERROR(VLOOKUP(A4;CheckList;1;FALSE)))
The unmatched items will get the formatting

Where the CheckList range is the list H, A, F in your example below. After
that you can copy the format from A4 to the remainder of your table.

Basics working: The cell value is being looked-up in the "CheckList" range
for an exact match, if it isn't found it will return an error.

regards,
Tieske

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I want to highlight entries in a table that occur in a seperate list.
>
> My table looks something like this:
>
>
> A B C D
> E A F G
> H D J K
>
>
> And the "list" which is just a range of cells looks like this:
>
> H
> A
> F
>
>
> I'm using Excel 2007. I tried selecting both ranges that represent
> the table and list and do conditional highlighting so that any
> duplicate entries are highlighted except there is one problem with the
> way that worked. Since my table has some duplicate entries itself it
> highlighted "A" (as I intended, because it was in the table and list)
> but highlighted "D" also (not as I intended, because it was in the
> table twice).
>
> Any hints as to how I might get what I want accomplished (Highlight
> cells in the table that occur in the list)?
>

On Feb 22, 2:39 pm, "Tieske" <n...@here.com> wrote:
> Assumed that your table below has the "A" value in cell A4; you can format
> the A4 cell with this formula;
>
> =ISERROR(VLOOKUP(A4;CheckList;1;FALSE))
> The matched items will get the formatting
>
> =NOT(ISERROR(VLOOKUP(A4;CheckList;1;FALSE)))
> The unmatched items will get the formatting
>
> Where the CheckList range is the list H, A, F in your example below. After
> that you can copy the format from A4 to the remainder of your table.
>
> Basics working: The cell value is being looked-up in the "CheckList" range
> for an exact match, if it isn't found it will return an error.
>
> regards,
> Tieske
>
> <jayknit...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> >I want to highlight entries in a table that occur in a seperate list.
>
> > My table looks something like this:
>
> > A B C D
> > E A F G
> > H D J K
>
> > And the "list" which is just a range of cells looks like this:
>
> > H
> > A
> > F
>
> > I'm using Excel 2007. I tried selecting both ranges that represent
> > the table and list and do conditional highlighting so that any
> > duplicate entries are highlighted except there is one problem with the
> > way that worked. Since my table has some duplicate entries itself it
> > highlighted "A" (as I intended, because it was in the table and list)
> > but highlighted "D" also (not as I intended, because it was in the
> > table twice).
>
> > Any hints as to how I might get what I want accomplished (Highlight
> > cells in the table that occur in the list)?- Hide quoted text -
>
> - Show quoted text -

Ok, that formula works great! Thanks.

Now how do I copy just the conditional highlighting formula around to
the other cells in my table? When I use the Format Painter the cells
which are effected by the conditional highlighting change as expected,
but the formula itself doesn't change. For example, I set up the
formula for cell A4 (the formula is =ISERROR(VLOOKUP(A4;CheckList;
1;FALSE))) then copy the formatting to cell A5 but the formula stays
the same (still referencing cell A4). My tabe is too big to update
these formulas manually! Any more hints?

Check the formula in CF.

Might be that Excel helped you out...not......by placing $ signs around the cell
reference.

i.e. $A$4 instead of A4 which you should have.

It is easiest when doing this to select all the cells first then enter the
formula in CF rather than painting around after.


Gord Dibben MS Excel MVP


On 22 Feb 2007 15:04:53 -0800, (E-Mail Removed) wrote:

>Ok, that formula works great! Thanks.
>
>Now how do I copy just the conditional highlighting formula around to
>the other cells in my table? When I use the Format Painter the cells
>which are effected by the conditional highlighting change as expected,
>but the formula itself doesn't change. For example, I set up the
>formula for cell A4 (the formula is =ISERROR(VLOOKUP(A4;CheckList;
>1;FALSE))) then copy the formatting to cell A5 but the formula stays
>the same (still referencing cell A4). My tabe is too big to update
>these formulas manually! Any more hints?

On Feb 22, 4:23 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Check the formula in CF.
>
> Might be that Excel helped you out...not......by placing $ signs around the cell
> reference.
>
> i.e. $A$4 instead of A4 which you should have.
>
> It is easiest when doing this to select all the cells first then enter the
> formula in CF rather than painting around after.
>
> Gord Dibben MS Excel MVP
>
> On 22 Feb 2007 15:04:53 -0800, jayknit...@gmail.com wrote:
>
>
>
> >Ok, that formula works great! Thanks.
>
> >Now how do I copy just the conditional highlighting formula around to
> >the other cells in my table? When I use the Format Painter the cells
> >which are effected by the conditional highlighting change as expected,
> >but the formula itself doesn't change. For example, I set up the
> >formula for cell A4 (the formula is =ISERROR(VLOOKUP(A4;CheckList;
> >1;FALSE))) then copy the formatting to cell A5 but the formula stays
> >the same (still referencing cell A4). My tabe is too big to update
> >these formulas manually! Any more hints?- Hide quoted text -
>
> - Show quoted text -

Thanks for the tips, I had to get all my relative and absolute
references correctly setup and things worked great!

On Feb 22, 2:39 pm, "Tieske" <n...@here.com> wrote:
> Assumed that your table below has the "A" value in cell A4; you can format
> the A4 cell with this formula;
>
> =ISERROR(VLOOKUP(A4;CheckList;1;FALSE))
> The matched items will get the formatting
>
> =NOT(ISERROR(VLOOKUP(A4;CheckList;1;FALSE)))
> The unmatched items will get the formatting
>
> Where the CheckList range is the list H, A, F in your example below. After
> that you can copy the format from A4 to the remainder of your table.
>
> Basics working: The cell value is being looked-up in the "CheckList" range
> for an exact match, if it isn't found it will return an error.
>
> regards,
> Tieske
>
> <jayknit...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> >I want to highlight entries in a table that occur in a seperate list.
>
> > My table looks something like this:
>
> > A B C D
> > E A F G
> > H D J K
>
> > And the "list" which is just a range of cells looks like this:
>
> > H
> > A
> > F
>
> > I'm using Excel 2007. I tried selecting both ranges that represent
> > the table and list and do conditional highlighting so that any
> > duplicate entries are highlighted except there is one problem with the
> > way that worked. Since my table has some duplicate entries itself it
> > highlighted "A" (as I intended, because it was in the table and list)
> > but highlighted "D" also (not as I intended, because it was in the
> > table twice).
>
> > Any hints as to how I might get what I want accomplished (Highlight
> > cells in the table that occur in the list)?- Hide quoted text -
>
> - Show quoted text -

Ok, now for a similar but different problem....

I have two tables, I want any value in table 1 to get highlighted that
appears in table 2. Again, table 1 may have duplicate values. Any
hints?

Thanks in advance!