Menu
I'm trying to have a cell on Sheet A check if it's either > or < the value in a cell on Sheet B, then change its color accordingly. Under the custom formula I use: =A1>("SheetB!A1"), but it doesn't seem to work. I use the color Green for the > and the color Red for the <. Every time the rules are saved it will always display A1 on Sheet A in red.
Is the function wrong? Or is it not possible to have a Conditional Format even search across sheets?
778
In earlier versions of Excel, you can't refer to another sheet in conditional formatting or data validation. With data validation, you can't even click on the other sheet, to try and create a reference. In conditional formatting, you can enter the reference in the dialog box, but then Excel yells at you.
Copy conditional formatting rules to another worksheet/workbook with Format Painter. Normally, we can copy the conditional formatting rule from one range to another worksheet/workbook with the Format Painter feature in Excel. 2. Shift to the destination range, and drag the paintbrush across this range.
The author replies: It's true that, if you want to use a formula within the Conditional Formatting function to compare the values of two cells from different worksheets, you will receive the error message: “You may not use references to other worksheets or workbooks for Conditional Formatting criteria.” However, you ...
For some reason (I confess I don't really know why) a custom formula in conditional formatting does not directly support cross-sheet references.
But cross-sheet references are supported INDIRECT-ly:
=A1>INDIRECT("SheetB!A1")
or if you want to compare A1:B10 on SheetA with A1:B10 on SheetB, then use:
=A1>INDIRECT("SheetB!A1:B10")
=A1>INDIRECT("SheetB!"&CELL("address",A1))
applied to range A1:B10.
172
You can do this by referencing the cell and row number in the current sheet, so as you drag-copy that conditional formatting to other rows it will reference the correct cells. In the below equation I am coloring cells based on the exact same cell in some other sheet named "otherSheetName" in this example. If for example you want to color cell B2 in Sheet2 if the cell B2 in otherSheetName contains the text "I Like Dogs" you would go to cell Sheet2!B2 , click condition formatting, choose equation from the drop down and paste the below equation.
=IF(INDIRECT("otherSheetName!"&ADDRESS(ROW();COLUMN()))="I Like Dogs";1;0) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
