• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting

Sejwal_Manish

New Member
I am looking to highlight cells based on the linked formula or as the
cell changes the linked sheet.
For instance, in the attached excel sheet, up until 2020 - cell values
are hardcoded, while from 2021 to 2023- they are based on sheet 2
while from 2024 onwards cell values are based on sheet 3. Currently I
have highlighted them manually.
Likewise, for row#6- cell values are based on formulae within the sheet but not linked to other sheet in the file.

Is there a way to highlight them by conditional formatting using some formulae?
 

Attachments

  • Sample.xlsx
    11.6 KB · Views: 1
You should be able to use FORMULATEXT in conditional formatting with the likes of:
=ISNUMBER(SEARCH("Sheet2!",FORMULATEXT(C2)))
I've done this for Sheet2 and Sheet3 in the attached in cells C2:AZ2 and it seems to work.
Looking for references to cells on the same sheet is more awkward. In the attached I've simply highlighted if a cell has a formula, but that is 'overwritten' by subsequent conditional formats if the formula contains a reference to another of the 2 sheets. See added formulae at cell AN2:AP2.
 

Attachments

  • Chandoo48183Sample.xlsx
    13 KB · Views: 4
p45cal - One more question - tried to apply the same ISNUMBER(SEARCH("Annual!",FORMULATEXT(D2))) to another excel file placed in a different location - basically another excel file and not some tab/sheet within the same file.
Gave the full location along with file name - however, doesn't seem to be working.
If possible can you do the same for 2 different sample files attached or another easier formulae for the separate excel file differentiation/highlight.
 

Attachments

  • Sample 2.xlsx
    11.6 KB · Views: 2
  • Sample.xlsx
    11.6 KB · Views: 1
The formula in a cell will include a reference such as:
='[Sample 2.xlsx]Sheet3'!$D$4
so you need to SEARCH for part of it, say Sample 2.xlsx or Sample 2.xlsx]Sheet3
If the workbook to which the formula points is closed it'll look different (including the full path to the workbook):
='C:\Users\Public\Downloads\[Sample 2.xlsx]Sheet3'!$D$4
but it shouldn't matter because it still contains those searched-for strings.
Your sample files contain no conditional formatting so I can't tell you where/how you're going wrong.
 
Back
Top