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

	Areas	types
		school	car	food	Date
row 1	Location1	150.00	500.00	200.00	4/9/2016
row 2	Location2	900.00	450.00	325.00	4/9/2016
row 3	Location3	436.00	543.00	764.00	4/9/2016
row 4	Location4	8,565.00	2,342.00	4,635.00	4/9/2016
row 5	Location5	967.00	876.00	65.00	4/9/2016
row 6	Location6	8,565.00	2,342.00	123.00	4/9/2016
row 7	Location7	904,345.00	8,765.00	6,546.00	4/9/2016

<colgroup><col><col><col span="3"><col></colgroup><tbody>
</tbody>

row 8	Location3	234.00	342.00	657.00	4/16/2016
row 9	Location1	554.00	8,797.00	23,423.00	4/16/2016
row 10	Location6	234.00	34,576.00	75.00	4/16/2016
row 11	Location4	967.00	6,456.00	655,464.00	4/16/2016
row 12	Location2	905.00	873.00	1,232.00	4/16/2016
row 13	Location5	2,347.00	345.00	4,546.00	4/16/2016
row 14	Location8	5,000.00	4,000.00	2,000.00	4/16/2016

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

row 15 Variance Date 4/9/2016 4/16/2016 Column A Column B Column C Colum D Column E row 16 Location1 404.00 8,297.00 23,223.00 row 17 Location2 5.00 423.00 907.00 row 18 Location3 -202.00 -201.00 -107.00 row 19 Location4 -7,598.00 4,114.00 650,829.00 row 20 Location5 1,380.00 -531.00 4,481.00 row 21 Location6 -8,331.00 32,234.00 -48.00 row 22 Location7 -904,345.00 -8,765.00 -6,546.00 row 23 Location8 5,000.00 4,000.00 2,000.00 <colgroup><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

​

</article>


​

<tbody>
</tbody>

 

[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;

Location1

150.00

500.00

200.00

4/9/2016

<tbody>
</tbody>

A1

B1

<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>
</tbody>

 

[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;

Location1

150.00

500.00

200.00

4/9/2016

<tbody>
</tbody>

A1

B1

<tbody>
</tbody>