Creating a highlighting formula

[JustinKredible]

Hi,

I'm an intermediate level excel user that has not had much experience
in writing formulas. I am wondering whether the following is possible:

I will has a centralised excel file with a list of items which will be
updated frequently. Each item will contain a unique ID, a description
etc.

I will have another larger excel file that will contain the same type
data. Is there a formula that can compare the 2 files and highlight the
entire row of any item on the larger spreadsheet that appears on the
centralised file.

Any help would be greatly appreciated.

 

[Guest]

Hi Justin

I can do this, not sure if it's the easiest way, but it's not difficult.

In the larger file, insert a column before column A.

Assume the data range in the smaller data set is called DataTable Assume
that the in the larger ID file the first column of data, which will now be in
Column B, is the ID number. In the first row of data in the larger file, put
in column A:

=IF(ISERROR(vlookup($B1,[Smaller Database Range],2,false)),"","x"))

Run this formula all the way to the bottom of the data set.

Now select all the data in the larger file, and go to Conditional
Formatting. Set Formula As:

=$A1="x"

and set your condition i.e. highlishgt text red, or fill yellow, or
whatever. (You can run the formula in column A of the larger file further for
future records, and then hide the column)

Good Luck!

Danny

 

[Dav]

Well you can do it all with a conditional format, however as conditiona
formating likes everything to be on the same sheet you need to set u
your range of unique id as a range on the other sheet. Lets say the
are in the range sheet2a1:a10, go to insert_name define and sedt thi
range to a name, in my example idrange is the name

then go to the first cell you are interested in for the format, in m
example A1, choose format_conditional formating, formula is
=NOT(ISERROR(VLOOKUP($A1,idrange,1,FALSE)))

and format to whatever colour you wish. this format can be copied t
the cells you wish to apply it to with paste_special formats

Regards

Da