Conditional Format in Excel

[jrobin5881]

I'm producing an outlier report and have a listing of offices in a column on an Excel Spreadsheet with data in the columns next to it. The list is 300 rows long and here's what I want to do. I want to to put a yellow highlight in cells where the office name match's a list on sheet 2. The list of possible matched office names is only about 25 long.
Example:
Sheet1
Office A 12widgets
Office B 10widgets (name would highlight due to it matching an office from the list in column A on sheet2)
Office C 11widgets
Office D 12widgets

I tried the normal conditional format menu command and it said I'm not allowed to select from a list or another sheet

 

[Zathras]

The way around this is to use a named range.

For example the conditional format formula

=MATCH(A2,YellowOffices,0)>0

works even though the range YellowOffices is on a different sheet.

 

[wwgmr]

Hi what you can do is first Name the range your looking up on the others sheet.

On say sheet two where your list is you want to check offices against is. Highlight the range or even the column then click Insert--> Name --- Define

Give the range a name. Now back on first sheet select first cell and click on conditional formating .
Change it to formula and enter this
=MATCH(A2,Name,FALSE)

Name = what ever name you gave the range on sheet 2.

Select what formating changes you want to happen and then drag the formula down the range on sheet one. It should work fine for you.


Hope this helps.

 

[Bowers74]

Welcome to Tek-Tips!!!

In case you have never created a named range:

1. Select the cells containing the Offices
2. In the Name Box (Upper-left hand corner where the active cell's reference is shown) enter "YellowOffices" and hit enter.

Then enter the Zathras' formula into the Conditional Formatting window. Don't forget to change the "Cell value is" to "Formula is"!

I hope this helps!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[jrobin5881]

should the A2 reference change with each cell change? In other words when the cell a5 on sheet1 is highlighted should the formula read =MATCH(A5,UNFUND,FALSE) UNFUND is what name I gave the list on sheet 2

 

[jrobin5881]

OOPS- Ok thanks everyone- My list on sheet2 was not an EXACT match so that's why the formatting didn't work at first but I got it going now. Thank you all for you help