Search code examples
excelexcel-formulaconcatenationconditional-formattingexcel-indirect

Highlighting data according to whether the same value turns up in another file

In Excel I want to highlight certain names in my table TAB1. My table looks like this (but is a lot longer):

Name    Surname
Luke    Skywalker
Han     Solo
Leia    Organa
...
(up to 50 names)

I also have another table TAB2 (saved in another Excel file) with SOME of the names. What I want is:

Whenever a name turns up in TAB2, its occurrence in TAB1 should be highlighted (color, or font size or such).

I read a (German) tutorial, but this gives me a mistake. My code is the following:

="VERGLEICH(A2;Teilnahmeliste ausgefüllt!B2:B51;0)"

(I'm working with the German version of Excel; VERGLEICH means compare in English; "Teilnahmeliste ausgefüllt" is the name of the second file, a.k.a. TAB2.)

I think my mistake is that I have not included the name of the spreadsheet (TAB2 has three spreadsheets; the one I need is called "Komplett").

Solution
    1. Yes, here it is no use pointing to a workbook containing several sheets without mention of which sheet.
    2. The real name of your "TAB2" workbook has a space in it, so in formulae references need to be enclosed - single inverted commas are usual.
    3. The full name of the workbook is very likely to include an extension (I assume .xlsx) and if so this needs to be part of the formula.
    4. Given the need for both workbook name and sheet name the syntax requires the use of brackets, so: '[Teilnahmeliste ausgefüllt.xlsx]Komplett'! to refer to a specific sheet in specific book.
    5. You say "My code is the following: ="VERGLEICH(A2;Teilnahmeliste ausgefüllt!B2:B51;0)"" but that is not code - the double inverted commas mean it is merely a string of text.
    6. As shown you do not have a specific 'key' - for example a means to differentiate between Luke (Skywalker) and say Luke (Evangelista) as well as Luke Skywalker and Nat Skywalker. A conventional solution to this is to create unique keys such as with:

    =A1&"|"&B1

    in C1 and copied down to suit (assuming Name is in A1 in both sheets). Often only keying the lookup array is required, as the corresponding key in the search formula may be created within the search formula "on the fly" however I recommend the above formula in both sheets.

    1. VERGLEICH means compare in English but the Excel function name in English is MATCH.
    2. The next issue is that Excel's Conditional Formatting will not normally 'span' workbooks:

    You may not use references to other workbooks for Conditional Formatting criteria.

    However, use of INDIRECT can be a workaround.

    1. Using INDIRECT does however introduce another issue - both workbooks must be open for the function to work effectively.
    2. So, with English function names and delimiter I think you should select ColumnC (in either sheet - depending upon which comparison you want) then
    3. HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

    =MATCH(C1,INDIRECT("'[Teilnahmeliste ausgefüllt.xlsx]Komplett'!$C:$C"),0)>0

    Format... with formatting of your choice, OK, OK.

    1. Where the German language and locale version for the formula may be:

    =VERGLEICH(C1;INDIREKT("'[Teilnahmeliste ausgefüllt.xlsx]Komplett'!$C:$C");0)>0