# Excel: compare items in different worksheets

Hi,

Excel 2016

I have a spread sheet with 4 worksheets.

worksheet1
worksheet2
worksheet3
worksheet4

worksheet4 has a long list of items, most of which are also in either 1,2 or 3.

How can I highlight the items in worksheet4 which do not appear in either 1,2 or 3?

TIA
Gareth
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Group Finance ManagerCommented:
I would suggest that you use Conditional Formatting with a Named Range and a Formula

I'll post some instructions if required
Compare-Across-Sheets.xlsx
0
Group Finance ManagerCommented:
1. Enter the names of the sheets to compare in the master sheet or an empty sheet. Then create a Named Range to refer to that Range. I have used "Sheets"

1. Select the cell(s) to be named
2. Click in the Name box, to the left of the formula bar
3. Type a valid one word name for the list, e.g. Sheets.
4. Press the Enter key.

2. This is the formula for the Conditional formatting =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!\$A\$1:\$A\$18"), A1))

The Named Range "Sheets" refers to the sheets to check
\$A\$1:\$A\$18 refers to the range to check, note the \$ signs make the range Absolute, i.e. a Fixed Range.
A1 is the cell to compare, because it is not Absolute the formula will check A1, B1, etc for each respective Row

3. Apply the Conditional Formatting Rule

To create conditional formatting rules with a formula
1. Select the cells to Format
2. Then from the Home Tab -> Conditional Formatting -> New Rule.
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
4. Under Format values where this formula is true, type the formula: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!\$A\$1:\$A\$18"), A1))>0
5. Click Format.
6. In the Color box, select a Fill Colour. In the Font Style box, select Bold.
7. Click OK until the dialog boxes are closed.

Adjust the Ranges and Sheet Names for your own WorkBook.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

CEO/OwnerAuthor Commented:
Thanks Roy, I will give this a go.
0
CEO/OwnerAuthor Commented:
Thanks Roy
0
Group Finance ManagerCommented: