Conditional Formatting: Trouble adding a col qualifier value into an existing formula

[TABLE="width: 1000, align: left"]

[TD="colspan: 10"]I have a live sample I can include somehow w/ Sheet 'G2-3' and Sheet 'Data'. I don't see a way to upload a file. I could upload it to my servers easy enough if needed.[/TD]

[TD="colspan: 3"]From Sheet 'G2-3'[/TD]

[TD="colspan: 3"]"Target Client Table"[/TD]

Col BB

Col BC

Col BD

Col BE

Col BF

Col BG

Reference

IL

0

iiii

224

773

312

rule #1

[TD="colspan: 3"]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]

TX

2

rule #2

[TD="colspan: 3"]Meets rule when State TX found in Column H of 'Data' sheet[/TD]

IN

1

iiii

260

574

812

rule #3

[TD="colspan: 3"]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]

OH

2

rule #4

[TD="colspan: 3"]Meets rule when State OH found in Column H of 'Data' sheet[/TD]

OH

-1

iiii

283

513

937

rule #5

[TD="colspan: 3"]Meets rule when any of these 3 Area Codes are found in Column S of 'Data' sheet[/TD]

MO

2

rule #6

[TD="colspan: 3"]Meets rule when State MO found in Column H of 'Data' sheet[/TD]

KY

3

rule #7

[TD="colspan: 3"]Meets rule when State KY found in Column H of 'Data' sheet[/TD]

MD

-1

rule #8

[TD="colspan: 3"]Meets rule when State MD found in Column H of 'Data' sheet[/TD]

CT

-1

rule #9

[TD="colspan: 3"]Meets rule when State CT found in Column H of 'Data' sheet[/TD]

MA

-1

rule #10

[TD="colspan: 3"]Meets rule when State MA found in Column H of 'Data' sheet[/TD]

RI

-1

rule #11

[TD="colspan: 3"]Meets rule when State RI found in Column H of 'Data' sheet[/TD]

[/TABLE]

[TABLE="width: 1000"]

[TD="width: 192, colspan: 3"]From Sheet 'Data'
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 92"][/TD]
[TD="width: 75"][/TD]

[TD="colspan: 3"]"Source Data Tables"[/TD]

[TD="width: 64"]Col H
[/TD]
[TD="width: 64"]Col S
[/TD]
[TD="width: 64"]/ZZ/
[/TD]
[TD="width: 64"]Col BQ
[/TD]
[TD="width: 64"]Col BR
[/TD]
[TD="width: 92"]Meets Rule
[/TD]

[TD="width: 64"]State[/TD]
[TD="width: 64"]Areacode[/TD]
[TD="width: 64"]/ZZ/
[/TD]
[TD="width: 64"]Kenny
[/TD]
[TD="width: 64"]Ron[/TD]
[TD="width: 92"][/TD]

[TD="width: 64"]G2-1[/TD]
[TD="width: 64"]G2-3
[/TD]

[TD="width: 64"]MI[/TD]
[TD="width: 64"]906[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]

N/A

[TD="width: 64"]TX[/TD]
[TD="width: 64"]409[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]MO[/TD]
[TD="width: 64"]903[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #6 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]OH[/TD]
[TD="width: 64"]740[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #4 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]OH[/TD]
[TD="width: 64"]283[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #5 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]OH[/TD]
[TD="width: 64"]937[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #5 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]MA[/TD]
[TD="width: 64"]517[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #10 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]TX[/TD]
[TD="width: 64"]832[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]IN[/TD]
[TD="width: 64"]574[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #3 ; Col BC = 1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline[/TD]

[TD="width: 64"]OH[/TD]
[TD="width: 64"]614[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]y[/TD]
[TD="colspan: 2"]rule #4 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]MD[/TD]
[TD="width: 64"]636[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"]y[/TD]
[TD="width: 64"][/TD]
[TD="colspan: 2"]rule #8 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]TX[/TD]
[TD="width: 64"]832[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"]y[/TD]
[TD="width: 64"][/TD]
[TD="colspan: 2"]rule #2 ; Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="width: 64"]IN[/TD]
[TD="width: 64"]812[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"]y[/TD]
[TD="width: 64"][/TD]
[TD="colspan: 2"]rule #3 ; Col BC = 1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline[/TD]

[TD="width: 64"]KY[/TD]
[TD="width: 64"]270[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"]y[/TD]
[TD="width: 64"][/TD]
[TD="colspan: 2"]rule #7 ; Col BC = 3 = .Pattern = xlGray16, RGB(71,255,163), Default text, Thin Outline[/TD]

[TD="width: 64"]CT[/TD]
[TD="width: 64"]860[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"]y[/TD]
[TD="width: 64"][/TD]
[TD="colspan: 2"]rule #9 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]MA[/TD]
[TD="width: 64"]413[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"]y[/TD]
[TD="width: 64"][/TD]
[TD="colspan: 2"]rule #10 ; Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[TD="width: 64"]IL[/TD]
[TD="width: 64"]773[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"]y[/TD]
[TD="width: 64"][/TD]
[TD="colspan: 2"]rule #1 ; Col BC = 0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline[/TD]

[TD="width: 64"]IL[/TD]
[TD="width: 64"]773[/TD]
[TD="width: 64"]/ZZ/[/TD]
[TD="width: 64"]y[/TD]
[TD="width: 64"][/TD]
[TD="colspan: 2"]rule #1 ; Col BC = 0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline[/TD]

[/TABLE]

[TABLE="width: 851"]

[TD="colspan: 3"]COLUMN BC RANKING[/TD]

[TD="colspan: 8"]Col BC = 0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline[/TD]

[TD="colspan: 8"]Col BC = 1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline[/TD]

[TD="colspan: 8"]Col BC = 2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline[/TD]

[TD="colspan: 8"]Col BC = 3 = .Pattern = xlGray16, RGB(71,255,163), Default text, Thin Outline[/TD]

[TD="colspan: 8"]Col BC = -1 = No.Pattern, Dark Blue Blackout RGB(54,96,146), White Text, No Outline[/TD]

[/TABLE]

The 1st formula looks at all the Area Codes listed, per "Target Client Table" sheet 'G2-3', in Column Cells BE1:BG15 and when it matches with an Area Code in a Row from Column S on the"Source Data Tables" 'Data' Sheet Conditional Formatting is added to highlight the blank Cells in Column BR, in this example. (i.e. "Applies to" = $BR$101:$BR$250) NOTE: A "y" or not in Column BQ/BR has no affect.

1st formula =COUNTIF(('G2-3'!$BE$1:$BO$15),$S101)

Target Sheet 'G2-3' Target Data Table 'BE3:BO15, Source Column 'S100' To Compare Data For True/False.

The 2nd formula looks at all the states listed, per "Target Client Table" sheet 'G2-3', in Column BB and when it matches with a state in a Row from Column H on the "Source Data Tables" 'Data' Sheet Conditional Formatting is added to highlight the blank Cells in Column BR, in this example. (i.e. "Applies to" = $BR$101:$BR$250) NOTE: A "y" or not in Column BQ/BR has no affect.

2nd formula =COUNTIF(('G2-3'!$BB$1:$BB$15),$H101)

Target Sheet 'G2-3' Target Data Table 'BB3:BB15, Source Column 'H100' To Compare Data For True/False.

***I NEED TO ADD THIS CHANGE TO BOTH FORMULAS:

Need to ADD Values = {0,1,2,3,-1} from the Client Sheet 'G2-3', in "Target Client Table" Column 'BC3:BC15', into the mix.

(-1s) are highest priorities and include a STOP, because those (via either State OR Area Code) are immediately darkened out, not to be used.

The rest in this order of highest to lowest priority is {0,1,2,3}. I was going to continue to use a heavier pattern (i.e. Pattern = xlGray50, xlGray25, xlGray16) and darker background color (shades of green) and lighten BOTH are the priority lowers with 3 the lightest pattern, xlGray16, and lightest green. (I have these all calculated and yes I recognize that this isn't contiguous with the initial rule that darkens out the very lowest 'zero' priority (-1)

I just need to code to recognize the priority Column Values = {0,1,2,3,-1} from Column 'BC3:BC15' WHEN there's first a match with STATE ($BB$1:$BB$15),$H101) or AREA CODE ($BE$1:$BO$15),$S101); into the existing formulas, so I can then apply the appropriate 'weighted' formatting in the Client's "Applies to" Columns, $BR$101:$BR$250.
.

Any ideas how to manipulate the main formulas to include reference to Column Values = {0,1,2,3,-1} in Column 'BC3:BC15' on the Client Sheet? In this case Sheet 'G2-3'

Also seen on this Forum post.

Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for

I know I over-complicated this! Sorry for those whom look at this problem.

Just before I was going to SIMPLIFY I did one more quick search and forgot ALL about COUNTIFS<<!!!

The solution is simply this...

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
i.e.

=COUNTIFS(B2:B7,">0", C2:C7,"=0")

My specific example:

=COUNTIFS('G2-1'!$BB$1:$BB$15,$H101, 'G2-1'!$BC$1:$BC$15,"-1")

Peace!

Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for

HELP!!

I ran into a problem using the "other" formula.

This one works perfectly, either way!

=COUNTIFS('G2-1'!$BB$1:$BB$15,$H101, 'G2-1'!$BC$1:$BC$15,""-1"")
=COUNTIFS('G2-1'!$BC$1:$BC$15,""-1"", 'G2-1'!$BB$1:$BB$15,$H101)

Basically, in addition to considering whether column 'G2-1'!$BC$1:$BC$15 is {-1,0,1,2,3}; it also only adds (conditional formatting) when a state in column H exists in the customer sheet (i.e. 'G2-1'!$BB$1:$BB$15)

The "other" formula works fine on it's own: =COUNTIF('G2-1'!$BE$1:$BO$15,$S101)

It adds (conditional formatting) when an area code in column S exists in the customer sheet (i.e. 'G2-1'!$BE$1:$BO$15)

BUT NOW ADDING IN THE BC COLUMN, IT DOESN'T WORK!
=COUNTIFS('G2-1'!$BC$1:$BC$15,""-1"", 'G2-1'!$BE$1:$BO$15,$S101)

I have NO idea why. The ONLY difference is the space where the formula looks for, in this case area codes.

In the first formula it only has to look in a portion of ONE column, and the 2nd formula it's looking into a table of 10 or so columns.

Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for

Quote from rory;799896

That is precisely the issue - the ranges in COUNTIFS must be the same size and shape. You could use SUMPRODUCT instead:

=SUMPRODUCT(('G2-2'!$BC$1:$BC$15=-1)*('G2-2'!$BE$1:$BO$15=$S101))

Wow, I see what you mean now. Oddly enough the areas can even be non-contiguous ranges, but for some dumb reason they have to be the exact same size and shape!

How would SUMPRODUCT work?

I think it has to be more like COUNTIFS, because it's looking for a 'condition' to apply certain formatting; and in this case two different conditions, not a product of the two.

Does anyone know a similar function to COUNTIFS that doesn't require the ranges it looks to compare data to be the exact same size and shape (# of rows and columns)?

In this case each row in Table 1 in Column S '=$S101', it's looking for a matching Area Code on Sheet G2-2 ('G2-2'!$BE$1:$BO$15...) but there's a second condition; whether Sheet G2-2 Column BC is {-1, 3, 2, 1, or 0) -- ('G2-2'!$BC$1:$BC$15=-1)

Here's a quick overview...

The first main sheet is called 'Data'. This omits non-essential columns for space in the example.

Table 1:

[TABLE="class: cms_table_grid, width: 850, align: left"]

[TD="align: center"]Col H
[/TD]
[TD="align: center"]Col S[/TD]
[TD="align: center"]Col T[/TD]
[TD="align: center"]Col Z[/TD]

[TD="align: center"]State
[/TD]
[TD="align: center"]Area Code
[/TD]
[TD="align: center"]State Group
[/TD]
[TD="align: center"]G2-2
[/TD]

CA

408

Group A

[TD="align: center"] (3)Lightest Green[/TD]

< Column with conditional formulas

OH

440

Group A

[TD="align: center"] -None-w/ Area Codes, none were listed.[/TD]

< Of course, "Applied To" in this case is $Z3:$Z17

KS

316

Group B

[TD="align: center"] -None-[/TD]

Based on next table...

IL

217

Group A

[TD="align: center"] (3)Lightest Green[/TD]

(See ex. color coding)

TX

214

Group A

[TD="align: center"] (-1)Blacked Out[/TD]

NE

402

Group B

[TD="align: center"] -None-[/TD]

TX

214

Group A

[TD="align: center"] (-1)Blacked Out[/TD]

MI

269

Group A

[TD="align: center"] (1)Med Green[/TD]

DC

202

Group A

[TD="align: center"] -None-[/TD]

OH

614

Group A

[TD="align: center"] (0)Dark Green-High[/TD]

TX

281

Group A

[TD="align: center"] (0)Dark Green-High[/TD]

TN

931

Group B

[TD="align: center"]-None-[/TD]

IN

765

Group A

[TD="align: center"] (2)Med Darker Green[/TD]

IA

641

Group B

[TD="align: center"] -None-[/TD]

FL

561

Group A

[TD="align: center"] (-1)Blacked Out[/TD]

[/TABLE]

In this case the other sheet is called 'G2-2'

=COUNTIFS('G2-2'!$BC$1:$BC$15,-1, 'G2-2'!$BE$1:$BO$15,$S101)

The trick is when there's no Area Codes it considers only State. It might take two formulas and using the STOP option. Maybe something like...
=COUNTIFS(AND('G2-2'!$BC$1:$BC$15,"-1",'G2-2'!$BD$1:$BD$15="", 'G2-2'!$BE$1:$BO$15,$S101))

And notice two TX entries. One is high priority Area Codes and the other is "-1" so those Area Codes are blacked out.

Again the priority order from highest to lowest is {0,1,2,3,-1) and the conditional formatting is applied appropriately.

Table 2:
[TABLE="class: cms_table_grid, width: 800, align: left"]

[TD="align: center"][/TD]
[TD="align: center"]Col BB[/TD]
[TD="align: center"]Col BC[/TD]
[TD="align: center"]Col BD[/TD]
[TD="align: center"]Col BE[/TD]
[TD="align: center"]Col BF[/TD]
[TD="align: center"]Col BG[/TD]
[TD="align: center"]To Col BO…[/TD]

Row 1

[TD="align: center"]CA[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]"list of all[/TD]
[TD="align: center"]408[/TD]
[TD="align: center"]415[/TD]
[TD="align: center"]510[/TD]

Row 2

[TD="align: center"]TX[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]area codes..."[/TD]
[TD="align: center"]281[/TD]
[TD="align: center"]713[/TD]
[TD="align: center"]832[/TD]

Row 3

[TD="align: center"]OH[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]380[/TD]
[TD="align: center"]614[/TD]
[TD="align: center"]513[/TD]

Row 4

[TD="align: center"]MI[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

Row 5

[TD="align: center"]IN[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

Row 6

[TD="align: center"]IL[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]309[/TD]
[TD="align: center"]217[/TD]
[TD="align: center"]618[/TD]
[TD="align: center"][/TD]

Row 7

[TD="align: center"]TX[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]214[/TD]
[TD="align: center"]469[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

Row 8

[TD="align: center"]FL[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

Row 9

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

Row 10

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

Row 11

Row 12

Row 13

Row 14

Row 15

[/TABLE]

Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for

In an effort to find the proper function...I was hoping on VLOOKUP, but it doesn't appear it does what I need. Maybe MATCH or the INDIRECT function in combination with the AND function and possibly a 'Nested' option? Any help with direction would be wonderful.

I have another outstanding post on this forum here, but as always I will update this forum with the solution when found.

Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for

When you get right down to it, it's really just as easy as an AND function.

=AND('G2-2'!$BE$1:$BO$15,$S101, 'G2-2'!$BC$1:$BC$15="-1")

Apply formatting to given cell (i.e. Column Z) IF that row's Column S (an Area Code) is found in the sheet & range of -- 'G2-2'!$BE$1:$BO$15
AND also in that same row, on that sheet, Column BC="-1" -- 'G2-2'!$BC$1:$BC$15="-1"

Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for

[Blocked Image: http://www.ozgrid.com/forum/images/misc/quote_icon.png] Originally Posted by wittonline [Blocked Image: http://www.ozgrid.com/forum/images/buttons/viewpost-right.png]
When you get right down to it, it's really just as easy as an AND function.
=AND('G2-2'!$BE$1:$BO$15,$S101, 'G2-2'!$BC$1:$BC$15="-1")

I don't think that formula does at all what you think it does.

This is just to try to explain what I need. SUMPRODUCT, well returns the SUMPRODUCT a numeric value. Not what I need.

=SUMPRODUCT(('G2-2'!$BC$1:$BC$15=-1)*('G2-2'!$BE$1:$BO$15=$S101))

What I need is conditional formatting so the formula needs to return similar to TRUE/FALSE therefore whether or not to apply formatting or not.

In my example above I applied your formula, just in case, to these two rows...

sheet: 'Data'
[TABLE="class: grid, width: 456, align: left"]

[TD="width: 64"]Col H[/TD]
[TD="width: 64"]Col S[/TD]
[TD="width: 64"]Col T[/TD]
[TD="width: 64"]Col Z[/TD]

State

Area Code

State Group

G2-2

TX

214

Group A

(-1)Blacked Out

TX

214

Group A

(-1)Blacked Out

[/TABLE]

...And the special formatting didn't apply.

Again, the idea is that IF Col BC=-1 from sheet 'G2-2' and Col S from sheet 'Data' (=$S101) contains a value within this range ('G2-2'!$BE$1:$BO$15) as seen below, THEN the statement is TRUE and the formatting is applied.

sheet: 'G2-2'
[TABLE="class: grid, width: 384, align: left"]

[TD="width: 64"][/TD]
[TD="width: 64"]Col BB
[/TD]
[TD="width: 64"]Col BC
[/TD]
[TD="width: 64"]Col BD[/TD]
[TD="width: 64"]Col BE[/TD]
[TD="width: 64"]Col BF
[/TD]

[TD="width: 64"]Row 7
[/TD]
[TD="width: 64"]TX[/TD]
[TD="width: 64"]-1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]214[/TD]
[TD="width: 64"]469
[/TD]

[/TABLE]

Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for

From the reduced tables on the post above, here's a live example:

Say the formula was in Col Z on 'Data' sheet and in a row with TX as State and 214 as Area Code in Col S as seen below. And since the corresponding row on sheet 'G2-2' Col BC=-1 from sheet 'G2-2' and the value '214' is also contained in sheet 'G2-2' within the range ('G2-2'!$BE$1:$BO$15) as seen below, SO the statement is TRUE and the formatting is applied.

Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for

Finally, here's a basic sample file. It took forever to clean out and find all the hidden refs and links. I would have been much better just making it from scratch.

http://www.onlineautomation.com/download/Workbook2.xlsm