Conditional Formatting and sumif or index or another formula? (please help!)

[aqalna]

<article>

I am working on a file and still need help for the following:

Is there a way to retrieve the information for "rows 16 to 23" under column "B" from where it populates the data based on "rows 1 to 14" under column "B"

Lastly, I am trying to figure out how can I highlight the "row 22" (Location 7) in yellow since it does not exist anymore from "rows 8 to 14"

As well, similar to "row 23" (location 8) where it is newly added and is not shown in "rows 8 to 14" so I want that to be highlighted in light blue.

Let me know if anyone can teach me in doing that please.

Here is my data set:
Column A Column B Column C Column D Column E



[TABLE="width: 941"]
<colgroup><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Areas[/TD]
[TD="colspan: 4"] types [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] school [/TD]
[TD] car [/TD]
[TD] food [/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]row 1[/TD]
[TD]Location1[/TD]
[TD] 150.00[/TD]
[TD] 500.00[/TD]
[TD] 200.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 2[/TD]
[TD]Location2[/TD]
[TD] 900.00[/TD]
[TD] 450.00[/TD]
[TD] 325.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 3[/TD]
[TD]Location3[/TD]
[TD] 436.00[/TD]
[TD] 543.00[/TD]
[TD] 764.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 4[/TD]
[TD]Location4[/TD]
[TD] 8,565.00[/TD]
[TD] 2,342.00[/TD]
[TD] 4,635.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 5[/TD]
[TD]Location5[/TD]
[TD] 967.00[/TD]
[TD] 876.00[/TD]
[TD] 65.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 6[/TD]
[TD]Location6[/TD]
[TD] 8,565.00[/TD]
[TD] 2,342.00[/TD]
[TD] 123.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 7[/TD]
[TD]Location7[/TD]
[TD] 904,345.00[/TD]
[TD] 8,765.00[/TD]
[TD] 6,546.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 792"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]row 8[/TD]
[TD]Location3[/TD]
[TD] 234.00[/TD]
[TD] 342.00[/TD]
[TD] 657.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 9[/TD]
[TD]Location1[/TD]
[TD] 554.00[/TD]
[TD] 8,797.00[/TD]
[TD] 23,423.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 10[/TD]
[TD]Location6[/TD]
[TD] 234.00[/TD]
[TD] 34,576.00[/TD]
[TD] 75.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 11[/TD]
[TD]Location4[/TD]
[TD] 967.00[/TD]
[TD] 6,456.00[/TD]
[TD] 655,464.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 12[/TD]
[TD]Location2[/TD]
[TD] 905.00[/TD]
[TD] 873.00[/TD]
[TD] 1,232.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 13[/TD]
[TD]Location5[/TD]
[TD] 2,347.00[/TD]
[TD] 345.00[/TD]
[TD] 4,546.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 14[/TD]
[TD]Location8
[/TD]
[TD] 5,000.00[/TD]
[TD] 4,000.00[/TD]
[TD] 2,000.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 502"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 611"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]row 15[/TD]
[TD]Variance Date[/TD]
[TD]4/9/2016[/TD]
[TD]4/16/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]


Column A

[/TD]
[TD] Column B
[/TD]
[TD] Column C
[/TD]
[TD] Colum D
[/TD]
[TD] Column E
[/TD]
[/TR]
[TR]
[TD]row 16[/TD]
[TD]Location1[/TD]
[TD="align: right"]404.00
[/TD]
[TD="align: right"]8,297.00[/TD]
[TD="align: right"]23,223.00
[/TD]
[/TR]
[TR]
[TD]row 17[/TD]
[TD]Location2[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]423.00[/TD]
[TD="align: right"]907.00[/TD]
[/TR]
[TR]
[TD]row 18[/TD]
[TD]Location3[/TD]
[TD="align: right"]-202.00[/TD]
[TD="align: right"]-201.00[/TD]
[TD="align: right"]-107.00[/TD]
[/TR]
[TR]
[TD]row 19[/TD]
[TD]Location4[/TD]
[TD="align: right"]-7,598.00
[/TD]
[TD="align: right"]4,114.00[/TD]
[TD="align: right"]650,829.00[/TD]
[/TR]
[TR]
[TD]row 20[/TD]
[TD]Location5[/TD]
[TD="align: right"]1,380.00[/TD]
[TD="align: right"]-531.00[/TD]
[TD="align: right"]4,481.00[/TD]
[/TR]
[TR]
[TD]row 21[/TD]
[TD]Location6[/TD]
[TD="align: right"]-8,331.00[/TD]
[TD="align: right"]32,234.00[/TD]
[TD="align: right"]-48.00[/TD]
[/TR]
[TR]
[TD]row 22[/TD]
[TD]Location7[/TD]
[TD="align: right"]-904,345.00[/TD]
[TD="align: right"]-8,765.00[/TD]
[TD="align: right"]-6,546.00[/TD]
[/TR]
[TR]
[TD]row 23[/TD]
[TD]Location8[/TD]
[TD="align: right"]5,000.00[/TD]
[TD="align: right"]4,000.00[/TD]
[TD="align: right"]2,000.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


​

</article>


​

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dachickenMind]

Here is what you can do:

1. if for row 8 to row 14, you can change the number to negative, the number for row 16 to row 23 can be retrieved from the formula "=SUMIF($A$1:$A$14,A16,$B$1:$B$14)"


2. To highlight the "row 22" (Location 7) in yellow , you need to first :select A16:A23, second: go to conditioning formatting , choose formula, copy paste the formula "=COUNTIF($A$8:$A$14,A16)=0" in there, and choose the yellow color as your desired color, hit OK button.

3.To highlight the "row 23" (Location 8) in blue , you need to first :select A16:A23, second: go to conditioning formatting , choose formula, copy paste the formula "=COUNTIF($A$1:$A$7,A16)=0" in there, and choose the blue color as your desired color( you can customize the color), hit OK button.

Let me know if you have any questions.

 

[aqalna]

The data is always updating and new locations will continue to come up and locations will disappear. So for your recommendation will this be dynamic or static conditional formatting....if it is static just for those two rows than it might not work :S


I am not looking to retrieve numbers but instead the areas being consolidated as a "Summary section"

so ie:

Location 1
Location 2
Location 3

It is basically summarizing the two sets of data that is based on dates into new summary.

I hope that makes more sense and you can help me out thanks again.

Here is what you can do:

1. if for row 8 to row 14, you can change the number to negative, the number for row 16 to row 23 can be retrieved from the formula "=SUMIF($A$1:$A$14,A16,$B$1:$B$14)"


2. To highlight the "row 22" (Location 7) in yellow , you need to first :select A16:A23, second: go to conditioning formatting , choose formula, copy paste the formula "=COUNTIF($A$8:$A$14,A16)=0" in there, and choose the yellow color as your desired color, hit OK button.

3.To highlight the "row 23" (Location 8) in blue , you need to first :select A16:A23, second: go to conditioning formatting , choose formula, copy paste the formula "=COUNTIF($A$1:$A$7,A16)=0" in there, and choose the blue color as your desired color( you can customize the color), hit OK button.

Let me know if you have any questions.

 

[aqalna]

so basically I want populate the rows 16 to row 23 (areas) in column B that is captured from rows "1 to 14"

 

[dachickenMind]

1.This is a dynamic conditional formatting
2. The formula SUMIF I give to you does the dynamic summary job. You just put the formula in the B16, and drag down, It will do its work.

 

[dachickenMind]

See the below:
The formula SUMIF I give to you does the dynamic summary job. You just put the formula in the B16, and drag down, It will do its work.
For B column:
=SUMIF($A$1:$A$14,A16,$B$1:$B$14)
For C column:
=SUMIF($A$1:$A$14,A16,$C$1:$C$14)
For D column:
=SUMIF($A$1:$A$14,A16,$D$1:$D$14)

so basically I want populate the rows 16 to row 23 (areas) in column B that is captured from rows "1 to 14"

 

[aqalna]

I am not sure if there is a way I can send you or attach my excel file. This is my first time trying to explain without using my excel file :S....

See the below:
The formula SUMIF I give to you does the dynamic summary job. You just put the formula in the B16, and drag down, It will do its work.
For B column:
=SUMIF($A$1:$A$14,A16,$B$1:$B$14)
For C column:
=SUMIF($A$1:$A$14,A16,$C$1:$C$14)
For D column:
=SUMIF($A$1:$A$14,A16,$D$1:$D$14)

 

[aqalna]

because those formula's arent working what is the a16 targeting and what is your column a and b? is column a the rows (row 1, row 2 etc...) and column b is areas (ie: location 1, location 2, locaiton 3, etc...)

 

[dachickenMind]

Location is in columnA;

[TABLE="class: cms_table, width: 941"]
<tbody>[TR]
[TD]Location1[/TD]
[TD]150.00[/TD]
[TD]500.00[/TD]
[TD]200.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 325"]
<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"] A1

[/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"]B1[/TD]
[TD="class: xl64, width: 65, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[aqalna]

I am not looking for to get amounts instead I am looking to get column "A".


So to give you more context that I have two sets of data with two sets of dates. It is basically consolidating the two sets of locations into one.

Does that make more sense?

Location is in columnA;

[TABLE="class: cms_table, width: 941"]
<tbody>[TR]
[TD]Location1[/TD]
[TD]150.00[/TD]
[TD]500.00[/TD]
[TD]200.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 325"]
<tbody>[TR]
[TD="width: 65"] A1
[/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"]B1[/TD]
[TD="class: xl64, width: 65, align: right"][/TD]
[/TR]
</tbody>[/TABLE]