aa aa
asked on
Conditional Formatting and sumif or index or another formula? (please help!)
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 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
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
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
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 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
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
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
Yes, you will indeed want to use SUMIFS to determine both the variance calculations on the bottom AND highlight cells where any location has no transactions (or zero total transactions) in the latter variance date.
Your data example doesn't include the header labels (Areas, School, Car, Food, Date) in row 1, so all your example references are off by one. So, I created an example workbook based on your data.
If, given that the balance data exists in rows 2:15, and the variance dates are in cells B17 and C17 (an extra blank row below), the formula for the variance for each location is:
=SUMIFS(B$2:B$15,$E$2:$E$1 5,$C$17,$A $2:$A$15,$ A18)-SUMIF S(B$2:B$15 ,$E$2:$E$1 5,$B$17,$A $2:$A$15,$ A18)
This is copied down and across as needed.
---------------------
As for the conditional formatting, the above formula has part of the rule needed, you only need to check if it is equal to zero:
=SUMIFS(B$2:B$15,$E$2:$E$1 5,$C$17,$A $2:$A$15,$ A18)=0
then apply that to all the cells with the SUMIFS, in the case of this example workbook, B18:D25
See the attached example file.
Regards,
Glenn
EE-Q_28976043.xlsx
Your data example doesn't include the header labels (Areas, School, Car, Food, Date) in row 1, so all your example references are off by one. So, I created an example workbook based on your data.
If, given that the balance data exists in rows 2:15, and the variance dates are in cells B17 and C17 (an extra blank row below), the formula for the variance for each location is:
=SUMIFS(B$2:B$15,$E$2:$E$1
This is copied down and across as needed.
---------------------
As for the conditional formatting, the above formula has part of the rule needed, you only need to check if it is equal to zero:
=SUMIFS(B$2:B$15,$E$2:$E$1
then apply that to all the cells with the SUMIFS, in the case of this example workbook, B18:D25
See the attached example file.
Regards,
Glenn
EE-Q_28976043.xlsx
ASKER
I just noticed I could attach a file ahh!!
sorry I didn't know we could attach ...would've made both our lives easier lol!
so the grey areas is what I need to populate
also i highlight the areas that I need help on conditional formatting.
Basically In the first set of data (rows 4 to 10) if Location 7 or any locations to be exact does not exist in the second set of data (rows 13 to 19) than highlight it yellow
and if the second set of data (rows 13 to 19) has a new location ie: location 8 , location 9 etc... and is not found in the first set of data (rows 4 to 10) than highlight them blue.
Than in the summary section (rows 32 to 39) have the respective "newly added" or "removed" to its colours
removed = yellow
newly added = blue
VarianceTemplatev2.xlsx
sorry I didn't know we could attach ...would've made both our lives easier lol!
so the grey areas is what I need to populate
also i highlight the areas that I need help on conditional formatting.
Basically In the first set of data (rows 4 to 10) if Location 7 or any locations to be exact does not exist in the second set of data (rows 13 to 19) than highlight it yellow
and if the second set of data (rows 13 to 19) has a new location ie: location 8 , location 9 etc... and is not found in the first set of data (rows 4 to 10) than highlight them blue.
Than in the summary section (rows 32 to 39) have the respective "newly added" or "removed" to its colours
removed = yellow
newly added = blue
VarianceTemplatev2.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Accept: Glenn Ray (https:#a41842902)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
frankhelk
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Accept: Glenn Ray (https:#a41842902)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
frankhelk
Experts-Exchange Cleanup Volunteer
https://support.office.com/en-us/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f