Solved conditional formatting excel sheet issue

May 9, 2014 at 08:20:41
Specs: Windows 7
good evening, i really find your explaination very valuable, but i'm a bit struggling with one issue you've explained before which i don't know to apply it.

simply i've a master tracker for 2000 companies for the whole department in the bank, as i'm a banker, my portfolio is 300 companies out of these 2000.

the bank generates one report in a form of excel sheet each and every month of the acheivements, but i've to spend every month not less than 4 hours searching name by name to allocate my total acheivement.

i understood from one discussion of yours earlier in 2013 that it suppose to be conditional formatting, which worked for me very well, but the thing is that i"ve to create 300 rules to be able to allocate all.

would you tell me how to manage to make my life easier, to be able to create one rule includes all my companies' names so it would color it all in one color so it would be easier for me?

thanks in advance

message edited by yahiayahia


See More: conditional formatting excel sheet issue

Report •


#1
May 9, 2014 at 09:08:47
create one rule includes all my companies' names

What you might try is using a Defined Name range with your Conditional Formatting.

WARNING, This will only work, if the two character strings you are trying to match are exactly the same.
So something like: Amiga Corporation is NOT the same as Amiga Corp.
Also, to Excel a Space is a legitimate charcter, so Amiga<space>Corporation is NOT the same as Amiga<space><space>Corporation even though they may appear the same.

First define your Name range:

List all of your company holdings in a column, as an example I will use column X
Select all the cells with data in column X, IE X1 - X300
On the ribbon, select the Formula Tab
In the Define Names section, Select Define Name
In the Name box enter a name for you selection, like NameList
In the Scope box, leave it at Workbook
In the Refers to box, it should show the range of cells you
selected, IE =sheet1!$X$1:$X$300
Click OK

Second define your Conditional Formatting

I do not know what column you will be checking against,
so I will use column A as an example.

1) Select your cell or range of cells, IE A1-A2000
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=COUNTIF(NameList,A1)

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

Now, any name in column A that matches your company holdings in column X will be highlighted in your selected color.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
May 9, 2014 at 10:21:04
✔ Best Answer
Here is a modified formula that may be a bit more versatile, it requires only a small change in your data.

For your Defined Name range, instead of using the complete company name, use only one key word,

So for Amiga Corporation, you would use only the word Amiga.

You may run into for something like:
Advance Auto Parts, Inc.
and
Advanced Micro Devices, Inc.

So in this case you many need to use the first two words, but try to keep it at a single word.

Also beware that your keyword should not appear inside any other company name, like:
ARAMARK Corporation
and
Mark Building Inc.

This may be harder to spot.

So, once you have your Defined Name range cells modified to display only a Key Word, your new Conditional Formatting

1) Select your cell or range of cells, IE A1-A2000
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=OR(ISNUMBER(SEARCH(NameList, A1)))

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#3
May 9, 2014 at 19:37:29
thank you so much, it was really helpful, the first solution is more doable in my case, it just needs one time of matching my list witht the way they generate the report, in terms of spelling and so, and then it would be an easy practice.

as when i tried the second solution, it highlighted the whole list as lots of common words exist.

anyways, thank you, i appreciate it.


Report •

Related Solutions


Ask Question