I have two sheets, SheetA for values, SheetB for calculations. I want a cell in SheetA to be colored, if a value in SheetB satisfies a condition.

Normally, referencing a field from another sheet is done with ='SheetB'!C1. However, this yields an "invalid formula" when put into the input box for conditional formatting with a formula.

Is it possible to reference a field in SheetB for conditional formatting in SheetA without the workaround of first mirroring the relevant values of SheetB in SheetA?

  • @pnuts: Hm, i suppose non-up-to-date CF doesn't suffice. I'm updating cell values (increment) which triggers a recalculation of a lot of stuff which in turn should trigger CF.
    – stefan
    Jun 27 '14 at 14:03

I think I was trying to achieve the result across spreadsheets before (rather than sheets). If you put a number in A1 of SheetB and in A1 of SheetA Conditional formatting... like so:

WA62909 example

then if you keep adding 1 to the value in SheetB!A1 the fill colours in SheetA!A1 should toggle red/pale blue.

The formulae I chose are of the type:

  • 1
    it works indeed! it's odd somehow that it's actually possible, but not with the analogous syntax. Thank you
    – stefan
    Jul 7 '14 at 22:38

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.