Hi,
I am having an issue using conditional formatting in Excel 2016.
I have a workbook that contains 2 worksheets. One sheet is called “Source data”, the other is called “Daily Report”, which is a report based on the “Source data”.
On the “Daily Report” worksheet, I want to grey out a set of cells based on data from the “Source Data” worksheet.
I wrote a conditional formatting function, located in the “Daily Report” worksheet, to make this happen:
=SUMIFS('Source Data'!$BN$2:$BN$50,'Source Data'!$AL$2:$AL$50,KF49)=0
This function looks for matches between cell KF49 of the “Daily Report” worksheet, and any cells in the array AL2:AL50 of the “Source data” worksheet and uses those matches to sum up corresponding cells in the array BN2:BN50 of the “Source Data worksheet.
If the sum is “0”, I want cells $KL$49:$LA$49 to be greyed out
When I apply this rule as a conditional format, all the cells in the array $KL$49:$LA$49 turn grey except for 2, KO49 and KQ49. I have removed all formatting from the worksheet except this single rule. This is really strange!
After much double and triple checking, I wondered if it had something to do with referencing between 2 worksheets. (I was desperate.) So, I created a column of cells in column KE of the “Daily report” worksheet with the same formula that I used in the conditional formatting rule.
=SUMIFS('Source Data'!$BN$2:$BN$50,'Source Data'!$AL$2:$AL$50,KF49)
I then created a conditional formula that simply looked for “0” in this set of cells and applied the same formatting to the same set of cells as above.
=$KE49=0
Applies to =$KL$49:$LA$49
It worked!
Why won’t it work for certain cells when it’s a conditional formatting rule, yet it works fine when it’s not part of the conditional formatting rules?
I am having an issue using conditional formatting in Excel 2016.
I have a workbook that contains 2 worksheets. One sheet is called “Source data”, the other is called “Daily Report”, which is a report based on the “Source data”.
On the “Daily Report” worksheet, I want to grey out a set of cells based on data from the “Source Data” worksheet.
I wrote a conditional formatting function, located in the “Daily Report” worksheet, to make this happen:
=SUMIFS('Source Data'!$BN$2:$BN$50,'Source Data'!$AL$2:$AL$50,KF49)=0
This function looks for matches between cell KF49 of the “Daily Report” worksheet, and any cells in the array AL2:AL50 of the “Source data” worksheet and uses those matches to sum up corresponding cells in the array BN2:BN50 of the “Source Data worksheet.
If the sum is “0”, I want cells $KL$49:$LA$49 to be greyed out
When I apply this rule as a conditional format, all the cells in the array $KL$49:$LA$49 turn grey except for 2, KO49 and KQ49. I have removed all formatting from the worksheet except this single rule. This is really strange!
After much double and triple checking, I wondered if it had something to do with referencing between 2 worksheets. (I was desperate.) So, I created a column of cells in column KE of the “Daily report” worksheet with the same formula that I used in the conditional formatting rule.
=SUMIFS('Source Data'!$BN$2:$BN$50,'Source Data'!$AL$2:$AL$50,KF49)
I then created a conditional formula that simply looked for “0” in this set of cells and applied the same formatting to the same set of cells as above.
=$KE49=0
Applies to =$KL$49:$LA$49
It worked!
Why won’t it work for certain cells when it’s a conditional formatting rule, yet it works fine when it’s not part of the conditional formatting rules?
