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.

[Danny Lewis]

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

"JustinKredible" wrote:

>
> 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.
>
>
> --
> JustinKredible
> ------------------------------------------------------------------------
> JustinKredible's Profile: http://www.excelforum.com/member.php...o&userid=36563
> View this thread: http://www.excelforum.com/showthread...hreadid=563120
>
>

[Dav]

Well you can do it all with a conditional format, however as conditional formating likes everything to be on the same sheet you need to set up your range of unique id as a range on the other sheet. Lets say they are in the range sheet2a1:a10, go to insert_name define and sedt this 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 my 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 to the cells you wish to apply it to with paste_special formats

Regards

Dav