Conditional Fornat rule referencing another sheet?

[I-Like-Excel]

Hello,

Was trying to create a conditional format rule that referenced values on another sheet of the workbook. Seems (at least) Excel 2016 conditional format rules recognise cell references on other sheets. Is there a workaround for this?

Regards

I-Like-Excel

EDIT: Ignore the second and third sentence. I was entering in teh formula rule wrongly and it was being "converted" to a text string instead (enclosed by ' '). Refer to sample file in post below.

[Pete_UK]

In older versions of Excel you can not directly reference cells or ranges in other sheets in a Conditional Formatting expression (I'm not sure about XL2021 or 365 versions). The way round it is to define a named range for the cells or range in that other sheet, and then to use the name within the CF expression.

Hope this helps.

Pete

[TMS]

Might help if you explained what you are trying to do. A formula would be helpful. What did you try? What did you get (or not get)?


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[TMS]

Pete will make it work if you give him a file

[Pete_UK]

Thanks for the vote of confidence, Trevor.

Pete

[I-Like-Excel]

Might help if you explained what you are trying to do. A formula would be helpful. What did you try? What did you get (or not get)?


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

I will be able to upload an example file when I get home today. EDIT: I now can get the CF rule window to acknowledge references to cells on another sheet. Not sure what I did differently. I got the rule/CF referencing the secondary sheet working applied to one cell on the primary sheet with a value in it. I am now trying to apply that same cell based VLOOKUP check CF to the other cells on the primary sheet.

[TMS]

@Pete: you're welcome

[I-Like-Excel]

OK, I have uploaded a sample file with a small scale version of what I am trying to do.

I cant write the CF for ONE cell, but cant seem to use relate references to copy it across to apply the same way to all cells

CF problem.xlsx

[Pete_UK]

See attached file, where I have set this up for you with these formulae:

Green Rule: =VLOOKUP(B4,Sheet2!$A$2:$B$11,2,FALSE)="P"

Red Rule: =VLOOKUP(B4,Sheet2!$A$2:$B$11,2,FALSE)="F"

Note also that these rules refer to the range $B$4:$F$5, so the B4 in the rules relate to the first cell within this range (and changes automatically for different columns/rows).

Hope this helps.

Pete

[I-Like-Excel]

I was sure I did/was doing exactly what you did here on the Excel file I was originally trying to get it all to work on when I first posted!

I got the rule equation working first for just one cell, but when I expanded the "Applies to" range, the formatting was being applied to ALL the selected cells in "Applied to" but based on just the test on the first cell (B$ in this example). ie. al the cells B4:F5 followed the format of what format B4 had, and not what the actual cell value was. I will need to triple check that again!

Thanks!

[Pete_UK]

You're welcome.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete