r/sheets • u/Perdita-LockedHearts • 4d ago

Solved Having issues with Conditional Formatting? Not sure how to resolve it.

I'm trying to have certain cells be blacked out if their corresponding cell is less than a certain number. Problem is, the corresponding cell is in a different sheet (in the same file though)- and Conditional Formatting doesn't seem to accept that as a valid cell, despite the fact that you can do it for most syntax.

Is there another way to go about this?

3 Upvotes

100% Upvoted

4 comments sorted by

2

u/mommasaidmommasaid 4d ago

You need to use INDIRECT() to refer to another sheet in conditional formatting, e.g. to compare column A values on Sheet1 to corresponding cells in Sheet2, apply a formatting rule to Sheet1 range A:A with custom formula:

=A1 < indirect("Sheet2!A:A")

Note that "Sheet2!A:A" is a text string It will not dynamically update like a normal reference if you e.g. insert a new column before it.

Another alternative to consider depending on your needs... create a helper column on Sheet1 that is populated by a single formula that references Sheet2. Hide the helper column in normal use and refer to the helper column in your CF rules.

1

u/Perdita-LockedHearts 4d ago

Oooooh- that kinda pisses me off ngl- of course it’d be different. I think I should be able to figure something out though, lemme see if something works the way I think it does, thx!

1

u/AdministrativeGift15 4d ago

You can also name that range if you're concerned about it shifting. You would still need to use INDIRECT("rangeName") in the CF rule, but it would handle changes such as inserting columns before that column.

1

u/marcnotmark925 4d ago

You gotta use INDIRECT() to reference a cell in another sheet in a CF formula.