Conditional formatting to show when data cell contents are used on other sheets

    Hello


    I am a beginner at more complicated functions in excel, so I hope my needs and explanations are adequate.


    I am trying to create a school curriculum mapping tool where data (teaching objectives) from several sources (Criteria sheets) are compiled into a summary sheet.


    Teachers will link (paste link?) several teaching objectives into a summary sheet.


    The complex part of the requirement is to show teachers whenever an objective has been used already by colouring the text (conditional formatting?) on the source sheet.


    I attach a model excel file which I hope shows the functionality of what I am trying to achieve, using simplified requirements. It depicts book or comic strip summaries that must choose setting/characters/page numbers or strip cell format based on fixed options. Whenever the summary sheet uses data from a source sheet, the data cell should change colour so other users can see it has been taken. Data cells can be used more than once.


    While I can get simple conditional formatting to work, one-cell to another .. I do not know which functions/programming are efficient to achieve my goal across all source sheets.


    Any and all help appreciated


    Thanks


    tomi_r

    Re: Conditional formatting to show when data cell contents are used on other sheets


    you could use a vlookup or count
    to get the true / false return i have wrapped in a not and iferror


    this will check both the stories and the comic strip


    =NOT(AND(ISERROR(VLOOKUP(A3,Stories!$C$1:$C$100,1,FALSE)), ISERROR(VLOOKUP(A3,'Comic Strip'!$C$1:$C$100,1,FALSE))))
    or
    =NOT(AND(ISNA(VLOOKUP(A3,Stories!$C$1:$C$100,1,FALSE)), ISNA(VLOOKUP(A3,'Comic Strip'!$C$1:$C$100,1,FALSE))))


    for the settings used on both sheets


    just to check the rules
    on the summary sheet stories and comics are the columns fixed
    so that a comic strip character will always be on column D in the stories sheet and on the comic sheet - if so that should be easy to change the lookup array


    see the attached spreadsheet - I have only formatted the settings sheet

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login