Formula with conditional formatting

[ELKassRO]

Hi guys,

Need some help please, I have SHEET1, say Column A, a list of 500 names of people

In SHEET2, I have in Column A, a list of 20 people.

I need a conditional formula where it highlights the names of people in SHEET1 that appear in SHEET2

I've gone brain dead today, despite using VLOOKUPs, MATCH, and INDEX formulas previously, without an issue

[FDibbins]

You would need Conditiona Formatting for this, but CF does not work across sheets in 2007

A way around this would be to reference teh data from sheet2 onto sheet1, then use that data for the CF

Give it a shot, If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[ELKassRO]

im on office 2010 =)

[FDibbins]

OK, then please update your profile accordingly

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =MATCH(A1,Sheet2!$A$1:$A$3,0)

Adjust your ranges as needed

[ELKassRO]

Thanks!

That worked...

I was sooo close, i had:

MATCH($A:$A,Sheet2!$A$1:$A$3,0)

I didn't know that conditional formatting automatically scans the next row down, hence why I discounted using A1

BTW what is the different between using:

$A$1 and
$A1

[FDibbins]

$ is used to absolute a cell reference, locking it so that when change when copied down or across. it comes in 3 different "flavors...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across