Forum Discussion
Excel Calculator with conditional format
Evening all, attaching a sample file with the formulas i already have for referencing other tabs, data pull in, data validation, etc. This is a calculator to assist my team in determining expected r...
Thanks JMB17, but if you click into any of the range B7:B10, you can see the CF formula still references the intended cells. In the snapshot below i've selected cell B8, and see the appropriate B4 and B3 references in the selection criteria. I would assume the remainder of B8-B10 would also fill black given that? Is there some other part to locking the reference i'm misunderstanding?
Absolute/relative cell references work the same in conditional formatting formulas as they do worksheet formulas.
So, no, cell B8 does not reference B3 and B4. Most likely, B7 was the active cell when you created the conditional format, so the CF formula applied to B8 is referencing B4 and B5 (and so on).
So, no, cell B8 does not reference B3 and B4. Most likely, B7 was the active cell when you created the conditional format, so the CF formula applied to B8 is referencing B4 and B5 (and so on).
- Having fixed that issue, the problem i'm having now is i need B7 to black out under two conditions:
1. cell B4 = "Coventry"
2. Cell B3 = "CHB" AND Cell B4 = "Kaiser"
This is currently written as: =OR($B$4="Coventry",AND($B$3="CHB",$B$4="Kaiser"))
It will only work (fill black) when cell B4 shows "Coventry". I've tried multiple versions, including two separate CF formulae, but of course nothing has yet worked.
Does anyone have any insight into how to make an evaluation statement like the above work for conditional formatting rules?- The formula looks good. Are you positive B3="CHB" and B4="Kaiser"? Are there any trailing spaces in those cell values?
If that's not the issue, you'll need to upload another copy of the workbook with the applicable worksheets.JMB17 I'm certain no trailing or leading spaces. Unless there's some issue resulting from the values in B3 and B4 being selected by means of data validation drop down lists?
Attaching an updated copy.