1

I am currently working on making a spreadsheet that will figure out which class I should be in based on times. That all works and outputs if I should be in the class to a TRUE/FALSE cell. My conditional formatting uses that output cell to highlight the cells on my class list that shows what class I am in. However, the formatting only applies to some of the cells when it should, and applies the formatting to some cells when it shouldn't, even though there is only one cell controlling the formatting for all the cells.

I cannot share the document because of how the school has set up our accounts, but I can show this screenshot:

the spreadsheet Edit: I made this dummy spreadsheed, the left box of C1:D8 has the custom formula of =A1, and the right box (F1:G8) has =EQ(A1,TRUE), and A1 is the bool TRUE.

  • I am afraid your image is not explanatory enough. Please create a dummy sheet with dummy data (but real formulas) and share it as view only. – marikamitsos Oct 25 '19 at 19:10
0

If I understood you correctly, you want the entire left "box" C1:D8 to turn black by conditional formatting. In order to achieve this, just change your custom formula of conditional formatting from =A1 to =$A$1

In order to achieve this same result on the right "box" F1:G8, change your custom formula from =EQ(A1,TRUE) to =EQ($A$1,TRUE)

By using $A you tell Google Sheets to preserve the reference to column A no matter to which column the conditional formatting formula is expanded to, and by using $1 you tell Google Sheets to preserve the reference to row 1 no matter to which row the conditional formatting formula is expanded to. If you don't add the $ character before A and 1, then the spreadsheet expands the cell reference and therefore the A1 reference at cell C1 is expanded to e.g. B1 at cell D1 and B2 at cell D2. Because e.g. B1 and B2 contain no data that makes sense in the conditional formatting formula, in such cases the formula returns FALSE and therefore no conditional formatting is applied. You prevent this from happening by using $A$1 in such formula.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.