Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a highlighting formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a highlighting formula
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a highlighting formula
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563120 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a complicated formula | Excel Worksheet Functions | |||
Creating charts from formula results | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a specific formula | New Users to Excel |