conditional format question

[gootroots]

I am looking to highlight duplicates in one workbook based on values from
another workbook

testbook! sheet1 A1:A10
values contain text string

linkedbook! sheet1 A1:A50
values contain text string

any duplicates in linkedbook! will be hightlighted in testbook!

I know how to use conditional formatting but need a little guidance when
linking to another workbook.

thank you in advance of any help

 

[Bernie Deitrick]

Your best bet would be to use a column of helper formulas:
=NOT(ISERROR(MATCH(A1,'[Linkedbook.xls]Sheet1'!$A$1:$A$50,False)))

and use those formulas returning TRUE as the basis of the formatting: in the Formula is, use =B1
(replace B1 with the first cell with the formula above).

HTH,
Bernie
MS Excel MVP

 

[Daryl S]

You can do this with an additional column in your testbook spreadsheet.

In the additional column in !testbook, do a VLOOKUP of the cell in column A
into the range of cells in column A of the !linkedbook spreadsheet, choosing
column 1 to be returned. This will result in the new column containing
either the same value as the cell in column A or #N/A if a match was not
found.

Now to use the conditional formatting, set the condition as "Cell Value Is"
"equal to" and the corresponding cell in the new column. Remove any $ that
show up by default. For example, if the new column with the VLOOKUPS is
column H, then the conditional formatting on cell A3 would be H3 (not $H$3).

That should do it.

 

[Gotroots]

Is vlookup the correct function to use as it is not returning the results I
am expecting

=LOOKUP(A1,[linkedbook.xlsx]sheet1!$A$1:$A$50)

If testbook! sheet1 A1 = apples

and

if apples is located in linkedbook! sheet1 A27 then apples will not return
alongside apples in testbook! sheet1 A1 in B1

perhaps I am missing a beat