conditional format question

  • Thread starter Thread starter gootroots
  • Start date Start date
G

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
 
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
 
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.
 
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
 
Back
Top