Conditional Underline

[Guest]

ok so in column B I have "Market" (Chicago, new york, etc.)
In column C I have Total Sales

The goals for total sales are different for each market. How do I Underline
the values in all cells that exceed a certain value depending on the market.

Example: Goal for Chicago is 4; Goal for New York is 7

Name Market Sales
John Chicago 4
Jed New York 6
Anne Chicago 5
Suzie Chicago 3
Joey New York 8

Market can be any 1 of 32 cities & Sales ranges a lot too. It takes me an
hour a day to sort through 200+ employees' stats to see who met goals and who
didnt, and then to Highlight the ones who did.

I would be willing to paste code that accomplishes this, or to use
conditional formatting but I can't figure out how to make that fit my search
conditions.

Please Help

 

[Guest]

In a separate worksheet in the same workbook as your daily results, create a
table with the Market in column A and the corresponding goal in column B.
Highlight the table and Insert > Name > Define and change the name to Goals;
hit OK.
Select cell C2 in your table of daily results. Format > Conditional
Formatting. In the first drop-down, select 'Formula Is'. In the text box,
make the condition read:
=C2>=VLOOKUP(B2,Goals,2). Click the Format... button and select Underline:
Single on the Font tab. Click OK twice. Copy that cell, then select the
rest of the cells in column C and Edit > Paste Special, select Formats and
click OK.

 

[Biff]

Hi!

You need to create a 2 column table that lists the market and its goal
(based on your sample data):

.................H...................I
1.........Market...........Goal
2........Chicago.............4
3......New York...........7

Then, assume your other data is in the range A2:C6.
Select the range A2:A6.
Goto the menu Format>Conditional Formatting
Select Formula Is and enter this formula in the box to the right:

=C2>=VLOOKUP(B2,H$2:I$3,2,0)

Click the Format button.
Select the style(s) desired.
OK out

Biff

 

[Guest]

Wow excellent reply. Very swift and precisely laid out in a manner which
solves my problem. I will have to make adjustments to fit it completely
right but it appears to do what i want. Thank you so much; I tried to
figure this out in Excel help but it confused me.

 

[Carim]

Hi John,

It is a two step proposal :
1. Create a reference table containing all your cities and their
related target numbers
2. Create conditional formatting for column C Total Sales as follows :
Format Conditional Formatting Formula Is
=IF(C4>=VLOOKUP(B2,$ReferenceTable$,2),1,0)
Format Underline (or Color)

HTH
Cheers
Carim

 

[Guest]

Thanks for the feedback. Note that the vlookup formulas that Biff and Carim
supplied have a fourth argument that makes the lookup more robust... Please
change the formula in the condition to read:
=C2>=VLOOKUP(B2,Goals,2,0)
That will ensure a proper match in the goals table. --Bruce