Conditional Formatting: Trouble adding an additional Column qualifier value into an existing formula.

[wittonlin]

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.

From Sheet 'G2-3'
"Target Client Table"

[TABLE="class: grid, width: 450, align: left"]
<tbody>[TR]
[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"][/TD]
[/TR]
[TR]
[TD="align: center"]IL[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]iiii[/TD]
[TD="align: center"]224[/TD]
[TD="align: center"]773[/TD]
[TD="align: center"]312[/TD]
[TD="align: center"]rule #1[/TD]
[/TR]
[TR]
[TD="align: center"]TX[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #2[/TD]
[/TR]
[TR]
[TD="align: center"]IN[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]iiii[/TD]
[TD="align: center"]260[/TD]
[TD="align: center"]574[/TD]
[TD="align: center"]812[/TD]
[TD="align: center"]rule #3[/TD]
[/TR]
[TR]
[TD="align: center"]OH[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #4[/TD]
[/TR]
[TR]
[TD="align: center"]OH[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]iiii[/TD]
[TD="align: center"]283[/TD]
[TD="align: center"]513[/TD]
[TD="align: center"]937[/TD]
[TD="align: center"]rule #5[/TD]
[/TR]
[TR]
[TD="align: center"]MO[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #6[/TD]
[/TR]
[TR]
[TD="align: center"]KY[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #7[/TD]
[/TR]
[TR]
[TD="align: center"]MD[/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"]rule #8[/TD]
[/TR]
[TR]
[TD="align: center"]CT[/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"]rule #9[/TD]
[/TR]
[TR]
[TD="align: center"]MA[/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"]rule #1 0[/TD]
[/TR]
[TR]
[TD="align: center"]RI[/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"]rule #1 1[/TD]
[/TR]
</tbody>[/TABLE]

​

From Sheet 'Data'
"Source Data Tables"


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Col H
[/TD]
[TD="align: center"]Col S
[/TD]
[TD="align: center"]/ZZ/
[/TD]
[TD="align: center"]Col BQ
[/TD]
[TD="align: center"]Col BR
[/TD]
[TD="align: center"]Meets Rule
[/TD]
[/TR]
[TR]
[TD="align: center"]State[/TD]
[TD="align: center"]Areacode[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]Kenny
G2-1[/TD]
[TD="align: center"]Ron
G2-3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]MI[/TD]
[TD="align: center"]906[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]N/A[/TD]
[/TR]
[TR]
[TD="align: center"]TX
[/TD]
[TD="align: center"]409[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #2 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]MO
[/TD]
[TD="align: center"]903[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #6 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]OH
[/TD]
[TD="align: center"]740[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #4 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]OH
[/TD]
[TD="align: center"]283
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #5[/TD]
[/TR]
[TR]
[TD="align: center"]OH
[/TD]
[TD="align: center"]937
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #5[/TD]
[/TR]
[TR]
[TD="align: center"]MA
[/TD]
[TD="align: center"]517[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #1 0[/TD]
[/TR]
[TR]
[TD="align: center"]TX
[/TD]
[TD="align: center"]832[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #2 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]IN
[/TD]
[TD="align: center"]574
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #3 ; Pattern = xlCrissCross[/TD]
[/TR]
[TR]
[TD="align: center"]OH
[/TD]
[TD="align: center"]614[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"][/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]rule #4 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]MD
[/TD]
[TD="align: center"]636[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #8[/TD]
[/TR]
[TR]
[TD="align: center"]TX
[/TD]
[TD="align: center"]832[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #2 ; Pattern = xlGray25[/TD]
[/TR]
[TR]
[TD="align: center"]IN
[/TD]
[TD="align: center"]812
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #3 ; Pattern = xlCrissCross[/TD]
[/TR]
[TR]
[TD="align: center"]KY
[/TD]
[TD="align: center"]270[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #7[/TD]
[/TR]
[TR]
[TD="align: center"]CT
[/TD]
[TD="align: center"]860[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #9[/TD]
[/TR]
[TR]
[TD="align: center"]MA
[/TD]
[TD="align: center"]413[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #1 0[/TD]
[/TR]
[TR]
[TD="align: center"]IL
[/TD]
[TD="align: center"]773
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #1 ; Pattern = xlGray50[/TD]
[/TR]
[TR]
[TD="align: center"]IL
[/TD]
[TD="align: center"]773
[/TD]
[TD="align: center"]/ZZ/[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]rule #1 ; Pattern = xlGray50[/TD]
[/TR]
</tbody>[/TABLE]










COLUMN BC RANKING

0 = .Pattern = xlGray50, RGB(51,204,51), Burgundy Text, Thin Outline
1 = .Pattern = xlCrissCross, RGB(0,255,0), Burgundy Text, Thin Outline
2 = .Pattern = xlGray25, RGB(204,255,102), Default text, Thin Outline
3 = .Pattern = xlGray16, RGB(71,255,163), Default text, Thin Outline

<tbody>
[TD="class: xl77"][/TD]

</tbody>

---

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.

 

[wittonlin]

Yes I keep up on this and ALWAYS post the result/answer when achieved.

There was an update on OzGrid.com: http://www.ozgrid.com/forum/showthread.php?t=205513&p=799910&posted=1#post799910

but no solution.

The update was that COUNTIFS, for some strange reason, must have the two ranges you're comparing data to be the EXACT same size and shape (#rows & #columns ). Wow.

So, I still need to find a function that does not have that restriction.

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? Any help with direction would be wonderful.

 

[wittonlin]

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"

 

[RoryA]

Yes I keep up on this and ALWAYS post the result/answer when achieved.

That's not the rule. The rule, in both places, is that you post links to your cross-posts. You did it there, but not here for some reason.

 

[wittonlin]

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

The idea is that on any particular row (i.e. Column Z in the example) where the formula resides on the 'Data' sheet; IF Col BC=-1 from sheet 'G2-2' and Col S from sheet 'Data' (=$S101) contains a value (an Area Code) within this range ('G2-2'!$BE$1:$BO$15) on sheet 'G2-2', as seen below, THEN the statement is TRUE and the formatting is applied.

https://www.mrexcel.com/forum/excel...-value-into-existing-formula.html#post4905890

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.

 

[wittonlin]

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

 

[wittonlin]

Please note the forum rules on cross-posting, which are the same here as elsewhere, and update your thread accordingly. Thank you.

Maybe I'm just in a mood, but I was thinking...

...so let me get this right, because I see this comment all over your boards....there's someone sitting there searching google for all the content, phrases and formulas just to see if they're posted in other places online? Wow, some people have way too much time on their hands.

 

[RoryA]

...so let me get this right, because I see this comment all over your boards....there's someone sitting there searching google for all the content, phrases and formulas just to see if they're posted in other places online? Wow, some people have way too much time on their hands.

No, there isn't, but many of us frequent multiple forums, just like you do, and we tend to spot these. It's a very simple rule to follow and based on common courtesy - why do you have a problem with that?