Intermittent conditional formatting

[Sam_Orton]

I have a fairly complex spreadsheet with A LOT of conditional formatting in it, and the conditional formatting works sometimes, and other times does not. It's conditional on a formula being true. Sometimes it appears to reference cells that are not in the formula. Other times the formula is true, but the formatting doesn't change.

The only consistency I have found in it is that if I do more than one cell at a time, some of them can be counted upon not to work.

Needless to say, this is losing its charm very quickly.

[#2 · Accepted solution]

It would help if you attached a sample of the sheet (with no confidential information)
Conditional formatting does have its challenges and the first is often understanding how it works.
You say: "Sometimes it appears to reference cells that are not in the formula."
and depending on the formula it WILL do this BY DESIGN. So if the 'Applied To' range is A1:D100 then the formula is applied to A1 and then "shifted" to each other cell modifying the cell references the same way formulas are changed when you copy or fill. So anything withOUT a '$' in front of it will shift and thing WITH a '$' will stay the same. SO if you want to highlight any of those rows if the value in column E is >100 then you could use the formula =$E1>100
That means for cell A1 (the top left corner of the Applied To range) will check if E1>100 and as it goes to B1,C1,D1 it will continue to check E1>100 because the $ before the E 'locks' the column E. But as it check A2, A3, A4, etc... it will check E2, E3, E4, etc... because there is NO $ before the '1'.
You say: "Other times the formula is true, but the formatting doesn't change."
but if the formatting doesn't change how do you KNOW the formula is true?
Another thing to check is that Excel tries to be smart about formatting rules and when you insert, delete or move cells, rows, columns it changes the Applies To range and copies the rule as it thinks best (which admittedly is often not what I think is best) and if you change or it THINKS you changed the Applies To range it will also adjust the formula references and sometimes really gets it wrong. There are many cases after I create or fix a rule that I have to go back in and fix the cell references, so double check that also.
Hope that helps but if you attach a sample we could help more.

[#3]

I'd be happy to attach a copy, but I don't see how. I don't see a command for it, and it won't let me drag and drop.

[#4]

if won't let you here then try using sharepoint, onedrive, or other cloud solution to share it or you can PM it to me (click my name and select message) here and I can post it

[#5]

@mtarler I will attach the file here but don't know what I'm looking at and what is right or wrong.  But from your message maybe you figured it out?

[#6]

So far it looks like it. If it turns out that no, I didn't figure it out, I'll get back to you. Thanks for your help.
This was probably too ambitious a project for someone whose experience with Excel stands at about 2 weeks now. But so far it seems to be working.

As for what you're looking at.... put a 1 in the cell to the left of any of the "cantrips" or "apprentice spells" on the "fundamentals" page. You will see changes in the columns labeled "action aspects" and "object aspects" corresponding to those listed to the right of the cantrip or apprentice spell you chose. So far, so good. If you look at the "Nuts&Bolts" page, you will also see corresponding changes there, but where the front page will add up the total of times that aspect is chosen, Nuts&Bolts will either have a one if that aspect is chosen, no matter how many times, or a zero if it hasn't been.
The pages in between should have the corresponding aspects also changing colors in accordance with the results of the Nuts&Bolts page. The Spell name should change from red to yellow to green according to whether none of the needed aspects are chosen, some of them are, or all of them are. If a 1 is put in the cell to the left of the spell name, the spell name should turn white.

The "Fundamentals" page, the "Professional Spells" page and the "Nuts&Bolts" page are working as intended. I have managed to get the aspects (apparently) working correctly in the intervening pages. Now I have to go back and get the spell names working in the intervening pages.

If none of that helps what you're looking at make any sense I'm sorry, that's the best I can explain it.

Again, thank you for your help.

Sam

[#7]

I'm very happy things seem to be working for you now. I will mark my explanation above as Best Response to 'close' this thread but of course you can 'undo' that by selecting the 3dots and selecting the 'this is not best answer' option.
As for how the sheet works, I guess that makes sense but I was more curious about which aspects weren't working and what they should be doing instead to zoom in on answer that issue, but sounds like you got it going.
Lastly, it is awesome you took on the project because Excel is a great skill to have and diving in on something your interested in makes the learning go so much better.